Re: Ok, who is EXCELLENT with queries?
Date: Thu, 5 Feb 2009 12:11:54 -0800 (PST)
Message-ID: <0e3b8d19-753d-4c19-a4fb-8dae8b40bf26_at_i20g2000prf.googlegroups.com>
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_DREPL_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_DREPL_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 Received on Thu Feb 05 2009 - 14:11:54 CST