Ok, who is EXCELLENT with queries?

From: Mtek <mtek_at_mtekusa.com>
Date: Thu, 5 Feb 2009 11:39:15 -0800 (PST)
Message-ID: <69908de0-60c3-4038-a717-bf6ac95c8af5_at_q30g2000prq.googlegroups.com>

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; Received on Thu Feb 05 2009 - 13:39:15 CST