Re: Ok, who is EXCELLENT with queries?
From: Mtek <mtek_at_mtekusa.com>
Date: Thu, 5 Feb 2009 13:40:42 -0800 (PST)
Message-ID: <84db8d1a-5dda-409f-b115-9d1afd2fac43_at_s9g2000prg.googlegroups.com>
On Feb 5, 2:11�pm, ddf <orat..._at_msn.com> wrote:
> On Feb 5, 1:39�pm, Mtek <m..._at_mtekusa.com> wrote:
>
>
>
> > I'm working on something for a client. �The query below basically
> > returns 1 record for the symbol he inputs. �All the tables basically
> > contain 1 record for each symbol except the history table, that
> > contains many per symbol hence the DENSE_RANK and limiting it to the
> > first item based on the DATE, ordered by.
>
> > Now, the first column is RANK. �What the customer now wants is to
> > compare that rank with the immediate previous one (based on the
> > ordered date) and have returned a string: �'UP','DOWN', or 'NONE'.
>
> > Not sure that can be done in a query anymore.......thoughts? �gonna
> > try some serious analytical functions here.
>
> > SELECT rank, recommendation, estimate, ind_rank, comp_ind,
> > last_report_file, rank_count, ind_code, last_rank_date
> > FROM (
> > � SELECT zrh.m_ticker,
> > � � � �NVL(dzr.z_rank_d, -9999) rank,
> > � � � �NVL(DECODE(zrc.cur_rec_value, 1, 'BUY', 3, 'HOLD', 5, 'SELL'),
> > 'N/A') recommendation,
> > � � � �NVL(tpec.value, -9999) estimate,
> > � � � �NVL(ia.ord_rank_d_rank, -9999) ind_rank,
> > � � � �NVL(ci.ord_rank_d_rank, -9999) comp_ind,
> > � � � �NVL(zrc.last_report_file, 'N/A') last_report_file,
> > � � � �NVL(ia.count_rank, -9999) rank_count,
> > � � � �NVL(ia.ind_code, -9999) ind_code,
> > � � � �NVL(TO_CHAR(zrh.time,'MM/DD/YYYY'),NULL) last_rank_date,
> > DENSE_RANK()
> > � OVER (PARTITION BY zrh.m_ticker ORDER BY zrh.time DESC) max_date
> > � FROM master_table mt,
> > � � � daily_zacks_rank dzr,
> > � � � zacks_rank_history zrh,
> > � � � zr_recom_cur zrc,
> > � � � trg_price_est_cur tpec,
> > � � � comp_ind ci,
> > � � � industry_agg ia
> > � WHERE mt.m_ticker = dzr.m_ticker (+)
> > � � AND mt.m_ticker = zrc.m_ticker (+)
> > � � AND mt.m_ticker = zrh.m_ticker (+)
> > � � AND mt.m_ticker = tpec.master (+)
> > � � AND mt.m_ticker = ci.m_ticker(+)
> > � � AND ci.ind_code = ia.ind_code (+)
> > � � AND zrh.m_ticker = 'IBM')
> > WHERE max_date = 1;
>
> If you're running 9.2.0.8 or later you can use either the LEAD() or LAG
> () function. �Examples follow.
>
> SQL> create table parts(
> � 2 � � � � �idpart number,
> � 3 � � � � �mydate date,
> � 4 � � � � �idcategory number,
> � 5 � � � � �hours number
> � 6 �);
>
> Table created.
>
> SQL>
> SQL> insert all
> � 2 �into parts
> � 3 �values (1, to_date('01/01/2001','MM/DD/YYYY'), 27, 7)
> � 4 �into parts
> � 5 �values (1, to_date('01/01/2001','MM/DD/YYYY'), 27, 5)
> � 6 �into parts
> � 7 �values (1, to_date('02/01/2001','MM/DD/YYYY'), 27, 2)
> � 8 �into parts
> � 9 �values (2, to_date('03/01/2001','MM/DD/YYYY'), 15, 8)
> �10 �into parts
> �11 �values (1, to_date('04/01/2001','MM/DD/YYYY'), 27, 5)
> �12 �into parts
> �13 �values (1, to_date('05/01/2001','MM/DD/YYYY'), 27, 6)
> �14 �select * From dual;
>
> 6 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select mydate, max(enddate), idcategory, ttlhrs
> � 2 �from
> � 3 �(select p.mydate, lead(p.mydate) over (order by p.mydate,
> p.idpart, p.idcategory) enddate, p.idcategory, d.ttlhrs
> � 4 � � � � �from parts p, (select mydate, sum(hours) ttlhrs from parts
> group by mydate) d
> � 5 � � � � �where d.mydate = p.mydate)
> � 6 �having max(enddate) is not null
> � 7 �group by mydate, idcategory, ttlhrs
> � 8 �/
>
> MYDATE � �MAX(ENDDA IDCATEGORY � � TTLHRS
> --------- --------- ---------- ----------
> 01-JAN-01 01-FEB-01 � � � � 27 � � � � 12
> 01-FEB-01 01-MAR-01 � � � � 27 � � � � �2
> 01-APR-01 01-MAY-01 � � � � 27 � � � � �5
> 01-MAR-01 01-APR-01 � � � � 15 � � � � �8
>
> SQL>
> SQL> -- with get_hrs as(
> SQL> -- � � �select idpart, mydate, idcategory, sum(hours) ttlhrs
> SQL> -- � � �from parts
> SQL> -- � � �group by idpart, mydate, idcategory
> SQL> -- )
> SQL> -- select get_hrs.mydate
> SQL>
> SQL> create table yakima(
> � 2 � � � � �username � � � �varchar2(30),
> � 3 � � � � �start_date � � �date,
> � 4 � � � � �end_date � � � �date
> � 5 �);
>
> Table created.
>
> SQL>
> SQL> insert all
> � 2 �into yakima
> � 3 �values('scott', to_date('01-sep-2008','dd-mon-rrrr'), to_date('30-
> sep-2008','dd-mon-rrrr'))
> � 4 �into yakima (username)
> � 5 �values('tiger')
> � 6 �select * from dual;
>
> 2 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select username,
> � 2 � � � � case when start_date is null then lag(end_date) over (order
> by end_date) else start_date end start_date,
> � 3 � � � � end_date
> � 4 �from yakima;
>
> USERNAME � � � � � � � � � � � START_DAT END_DATE
> ------------------------------ --------- ---------
> scott � � � � � � � � � � � � �01-SEP-08 30-SEP-08
> tiger � � � � � � � � � � � � �30-SEP-08
>
> SQL>
> SQL> create table gronkenflotz(
> � 2 � � � � �product_id � � �number,
> � 3 � � � � �product_name � �varchar2(35),
> � 4 � � � � �to_mkt_dt � � � date,
> � 5 � � � � �off_mkt_dt � � �date,
> � 6 � � � � �repl_prod � � � number
> � 7 �);
>
> Table created.
>
> SQL>
> SQL> insert all
> � 2 �into gronkenflotz
> � 3 �values(47, 'Poodle Buffer', to_date('01-jun-1978','dd-mon-rrrr'),
> to_date('29-mar-1978','dd-mon-rrrr'), null)
> � 4 �into gronkenflotz
> � 5 �values(71, 'Raisin De-Wrinkler', to_date('01-jun-1979','dd-mon-
> rrrr'), to_date('27-feb-1980','dd-mon-rrrr'), 347)
> � 6 �into gronkenflotz
> � 7 �values(347, 'Noodle Straightener', to_date('01-jun-1967','dd-mon-
> rrrr'), to_date('28-feb-1968','dd-mon-rrrr'), 47)
> � 8 �into gronkenflotz
> � 9 �values(973, 'Macaroni De-Elbowifier', null, null, 71)
> �10 �select * From dual;
>
> 4 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select product_id, product_name,
> � 2 � � � � �to_mkt_dt,
> � 3 � � � � �off_mkt_dt,
> � 4 � � � � �repl_prod
> � 5 �from
> � 6 � � � � �gronkenflotz
> � 7 �connect by repl_prod = prior product_id
> � 8 �start with product_id = 47;
>
> PRODUCT_ID PRODUCT_NAME � � � � � � � � � � � �TO_MKT_DT OFF_MKT_D
> REPL_PROD
> ---------- ----------------------------------- --------- ---------
> ----------
> � � � � 47 Poodle Buffer � � � � � � � � � � � 01-JUN-78 29-MAR-78
> � � � �347 Noodle Straightener � � � � � � � � 01-JUN-67 28-
> FEB-68 � � � � 47
> � � � � 71 Raisin De-Wrinkler � � � � � � � � �01-JUN-79 27-
> FEB-80 � � � �347
> � � � �973 Macaroni De-
> Elbowifier � � � � � � � � � � � � � � � � � � � � �71
>
> SQL>
> SQL> select product_id, product_name,
> � 2 � � � � �case when to_mkt_dt is null then lag(off_mkt_dt) over
> (order by repl_prod desc) else to_mkt_dt end to_mkt_dt,
> � 3 � � � � �off_mkt_dt,
> � 4 � � � � �repl_prod
> � 5 �from
> � 6 �(select product_id, product_name,
> � 7 � � � � �to_mkt_dt,
> � 8 � � � � �off_mkt_dt,
> � 9 � � � � �repl_prod
> �10 �from
> �11 � � � � �gronkenflotz
> �12 �connect by repl_prod = prior product_id
> �13 �start with product_id = 47);
>
> PRODUCT_ID PRODUCT_NAME � � � � � � � � � � � �TO_MKT_DT OFF_MKT_D
> REPL_PROD
> ---------- ----------------------------------- --------- ---------
> ----------
> � � � � 47 Poodle Buffer � � � � � � � � � � � 01-JUN-78 29-MAR-78
> � � � � 71 Raisin De-Wrinkler � � � � � � � � �01-JUN-79 27-
> FEB-80 � � � �347
> � � � �973 Macaroni De-Elbowifier � � � � � � �27-
> FEB-80 � � � � � � � � � 71
> � � � �347 Noodle Straightener � � � � � � � � 01-JUN-67 28-
> FEB-68 � � � � 47
>
> SQL>
>
> Hopefully you'll get the idea.
>
> David Fitzjarrell
Date: Thu, 5 Feb 2009 13:40:42 -0800 (PST)
Message-ID: <84db8d1a-5dda-409f-b115-9d1afd2fac43_at_s9g2000prg.googlegroups.com>
On Feb 5, 2:11�pm, ddf <orat..._at_msn.com> wrote:
> On Feb 5, 1:39�pm, Mtek <m..._at_mtekusa.com> wrote:
>
>
>
> > I'm working on something for a client. �The query below basically
> > returns 1 record for the symbol he inputs. �All the tables basically
> > contain 1 record for each symbol except the history table, that
> > contains many per symbol hence the DENSE_RANK and limiting it to the
> > first item based on the DATE, ordered by.
>
> > Now, the first column is RANK. �What the customer now wants is to
> > compare that rank with the immediate previous one (based on the
> > ordered date) and have returned a string: �'UP','DOWN', or 'NONE'.
>
> > Not sure that can be done in a query anymore.......thoughts? �gonna
> > try some serious analytical functions here.
>
> > SELECT rank, recommendation, estimate, ind_rank, comp_ind,
> > last_report_file, rank_count, ind_code, last_rank_date
> > FROM (
> > � SELECT zrh.m_ticker,
> > � � � �NVL(dzr.z_rank_d, -9999) rank,
> > � � � �NVL(DECODE(zrc.cur_rec_value, 1, 'BUY', 3, 'HOLD', 5, 'SELL'),
> > 'N/A') recommendation,
> > � � � �NVL(tpec.value, -9999) estimate,
> > � � � �NVL(ia.ord_rank_d_rank, -9999) ind_rank,
> > � � � �NVL(ci.ord_rank_d_rank, -9999) comp_ind,
> > � � � �NVL(zrc.last_report_file, 'N/A') last_report_file,
> > � � � �NVL(ia.count_rank, -9999) rank_count,
> > � � � �NVL(ia.ind_code, -9999) ind_code,
> > � � � �NVL(TO_CHAR(zrh.time,'MM/DD/YYYY'),NULL) last_rank_date,
> > DENSE_RANK()
> > � OVER (PARTITION BY zrh.m_ticker ORDER BY zrh.time DESC) max_date
> > � FROM master_table mt,
> > � � � daily_zacks_rank dzr,
> > � � � zacks_rank_history zrh,
> > � � � zr_recom_cur zrc,
> > � � � trg_price_est_cur tpec,
> > � � � comp_ind ci,
> > � � � industry_agg ia
> > � WHERE mt.m_ticker = dzr.m_ticker (+)
> > � � AND mt.m_ticker = zrc.m_ticker (+)
> > � � AND mt.m_ticker = zrh.m_ticker (+)
> > � � AND mt.m_ticker = tpec.master (+)
> > � � AND mt.m_ticker = ci.m_ticker(+)
> > � � AND ci.ind_code = ia.ind_code (+)
> > � � AND zrh.m_ticker = 'IBM')
> > WHERE max_date = 1;
>
> If you're running 9.2.0.8 or later you can use either the LEAD() or LAG
> () function. �Examples follow.
>
> SQL> create table parts(
> � 2 � � � � �idpart number,
> � 3 � � � � �mydate date,
> � 4 � � � � �idcategory number,
> � 5 � � � � �hours number
> � 6 �);
>
> Table created.
>
> SQL>
> SQL> insert all
> � 2 �into parts
> � 3 �values (1, to_date('01/01/2001','MM/DD/YYYY'), 27, 7)
> � 4 �into parts
> � 5 �values (1, to_date('01/01/2001','MM/DD/YYYY'), 27, 5)
> � 6 �into parts
> � 7 �values (1, to_date('02/01/2001','MM/DD/YYYY'), 27, 2)
> � 8 �into parts
> � 9 �values (2, to_date('03/01/2001','MM/DD/YYYY'), 15, 8)
> �10 �into parts
> �11 �values (1, to_date('04/01/2001','MM/DD/YYYY'), 27, 5)
> �12 �into parts
> �13 �values (1, to_date('05/01/2001','MM/DD/YYYY'), 27, 6)
> �14 �select * From dual;
>
> 6 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select mydate, max(enddate), idcategory, ttlhrs
> � 2 �from
> � 3 �(select p.mydate, lead(p.mydate) over (order by p.mydate,
> p.idpart, p.idcategory) enddate, p.idcategory, d.ttlhrs
> � 4 � � � � �from parts p, (select mydate, sum(hours) ttlhrs from parts
> group by mydate) d
> � 5 � � � � �where d.mydate = p.mydate)
> � 6 �having max(enddate) is not null
> � 7 �group by mydate, idcategory, ttlhrs
> � 8 �/
>
> MYDATE � �MAX(ENDDA IDCATEGORY � � TTLHRS
> --------- --------- ---------- ----------
> 01-JAN-01 01-FEB-01 � � � � 27 � � � � 12
> 01-FEB-01 01-MAR-01 � � � � 27 � � � � �2
> 01-APR-01 01-MAY-01 � � � � 27 � � � � �5
> 01-MAR-01 01-APR-01 � � � � 15 � � � � �8
>
> SQL>
> SQL> -- with get_hrs as(
> SQL> -- � � �select idpart, mydate, idcategory, sum(hours) ttlhrs
> SQL> -- � � �from parts
> SQL> -- � � �group by idpart, mydate, idcategory
> SQL> -- )
> SQL> -- select get_hrs.mydate
> SQL>
> SQL> create table yakima(
> � 2 � � � � �username � � � �varchar2(30),
> � 3 � � � � �start_date � � �date,
> � 4 � � � � �end_date � � � �date
> � 5 �);
>
> Table created.
>
> SQL>
> SQL> insert all
> � 2 �into yakima
> � 3 �values('scott', to_date('01-sep-2008','dd-mon-rrrr'), to_date('30-
> sep-2008','dd-mon-rrrr'))
> � 4 �into yakima (username)
> � 5 �values('tiger')
> � 6 �select * from dual;
>
> 2 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select username,
> � 2 � � � � case when start_date is null then lag(end_date) over (order
> by end_date) else start_date end start_date,
> � 3 � � � � end_date
> � 4 �from yakima;
>
> USERNAME � � � � � � � � � � � START_DAT END_DATE
> ------------------------------ --------- ---------
> scott � � � � � � � � � � � � �01-SEP-08 30-SEP-08
> tiger � � � � � � � � � � � � �30-SEP-08
>
> SQL>
> SQL> create table gronkenflotz(
> � 2 � � � � �product_id � � �number,
> � 3 � � � � �product_name � �varchar2(35),
> � 4 � � � � �to_mkt_dt � � � date,
> � 5 � � � � �off_mkt_dt � � �date,
> � 6 � � � � �repl_prod � � � number
> � 7 �);
>
> Table created.
>
> SQL>
> SQL> insert all
> � 2 �into gronkenflotz
> � 3 �values(47, 'Poodle Buffer', to_date('01-jun-1978','dd-mon-rrrr'),
> to_date('29-mar-1978','dd-mon-rrrr'), null)
> � 4 �into gronkenflotz
> � 5 �values(71, 'Raisin De-Wrinkler', to_date('01-jun-1979','dd-mon-
> rrrr'), to_date('27-feb-1980','dd-mon-rrrr'), 347)
> � 6 �into gronkenflotz
> � 7 �values(347, 'Noodle Straightener', to_date('01-jun-1967','dd-mon-
> rrrr'), to_date('28-feb-1968','dd-mon-rrrr'), 47)
> � 8 �into gronkenflotz
> � 9 �values(973, 'Macaroni De-Elbowifier', null, null, 71)
> �10 �select * From dual;
>
> 4 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select product_id, product_name,
> � 2 � � � � �to_mkt_dt,
> � 3 � � � � �off_mkt_dt,
> � 4 � � � � �repl_prod
> � 5 �from
> � 6 � � � � �gronkenflotz
> � 7 �connect by repl_prod = prior product_id
> � 8 �start with product_id = 47;
>
> PRODUCT_ID PRODUCT_NAME � � � � � � � � � � � �TO_MKT_DT OFF_MKT_D
> REPL_PROD
> ---------- ----------------------------------- --------- ---------
> ----------
> � � � � 47 Poodle Buffer � � � � � � � � � � � 01-JUN-78 29-MAR-78
> � � � �347 Noodle Straightener � � � � � � � � 01-JUN-67 28-
> FEB-68 � � � � 47
> � � � � 71 Raisin De-Wrinkler � � � � � � � � �01-JUN-79 27-
> FEB-80 � � � �347
> � � � �973 Macaroni De-
> Elbowifier � � � � � � � � � � � � � � � � � � � � �71
>
> SQL>
> SQL> select product_id, product_name,
> � 2 � � � � �case when to_mkt_dt is null then lag(off_mkt_dt) over
> (order by repl_prod desc) else to_mkt_dt end to_mkt_dt,
> � 3 � � � � �off_mkt_dt,
> � 4 � � � � �repl_prod
> � 5 �from
> � 6 �(select product_id, product_name,
> � 7 � � � � �to_mkt_dt,
> � 8 � � � � �off_mkt_dt,
> � 9 � � � � �repl_prod
> �10 �from
> �11 � � � � �gronkenflotz
> �12 �connect by repl_prod = prior product_id
> �13 �start with product_id = 47);
>
> PRODUCT_ID PRODUCT_NAME � � � � � � � � � � � �TO_MKT_DT OFF_MKT_D
> REPL_PROD
> ---------- ----------------------------------- --------- ---------
> ----------
> � � � � 47 Poodle Buffer � � � � � � � � � � � 01-JUN-78 29-MAR-78
> � � � � 71 Raisin De-Wrinkler � � � � � � � � �01-JUN-79 27-
> FEB-80 � � � �347
> � � � �973 Macaroni De-Elbowifier � � � � � � �27-
> FEB-80 � � � � � � � � � 71
> � � � �347 Noodle Straightener � � � � � � � � 01-JUN-67 28-
> FEB-68 � � � � 47
>
> SQL>
>
> Hopefully you'll get the idea.
>
> David Fitzjarrell
Yeah, my fault. 10g.
I'll review and test your query.....thanks! Received on Thu Feb 05 2009 - 15:40:42 CST