Re: Ok, who is EXCELLENT with queries?

From: Mtek <mtek_at_mtekusa.com>
Date: Thu, 5 Feb 2009 11:43:53 -0800 (PST)
Message-ID: <009d29a9-1130-4a11-8e84-3ceaf6f3fe85_at_b38g2000prf.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;

Well, I guess one option is an INLINE function of sorts..... Received on Thu Feb 05 2009 - 13:43:53 CST