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;
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