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