interday_tick_qry2.sql

INSTRUMENT_ID   TRADING_SYMBOL     PER_LOSER                 LOSER_RANK
          563   AVR                -81.5571071005137663021            1
          856   BGY                -80.6655624152989007679            2
          570   AVY                -80.2567073714234780283            3
          363   ANZ                -79.5938670411985018726            4
          686   BAK                -79.0841760432986754023            5
          1     AAB                -79.0836653386454183266            6
          663   AZN                -78.7587024144571174640            7
          438   AQW                -78.2416192283364958886            8
          7     AAH                -78.0521509252583513578            9
          145   AFP                -78.0325940860215053763           10

commit;

BEGIN
--Determine the top 10 percentage losers for the specified date on the
--specified exchanges sorted by percentage loss. The loss is calculated
--as a percentage of the last trade price of the previous day.


Select INSTRUMENT_ID, TRADING_SYMBOL, TRADE_PRICE, TRADE_DATE into #temp_tick3a
FROM  DBA.STOCK_TRADE st, (Select INSTRUMENT_ID AS idx, max(TRADE_TIME) AS maxtime
from DBA.STOCK_TRADE where TRADE_DATE = '2005-11-14'
group by INSTRUMENT_ID) y
WHERE  st.TRADE_DATE =  '2005-11-14'
AND st.TRADE_TIME = maxtime and idx = st.INSTRUMENT_ID
;

create variable prev_day date;
set prev_day = (Select MAX(TRADE_DATE) from STOCK_HISTORY
where TRADE_DATE < '2005-11-14');

SELECT TOP 10 INSTRUMENT_ID,TRADING_SYMBOL, PER_LOSER, LOSER_RANK
FROM   (SELECT INSTRUMENT_ID, TRADING_SYMBOL, PER_LOSER,
RANK() OVER (ORDER BY per_loser ASC) LOSER_RANK
FROM   (SELECT t.INSTRUMENT_ID,t.TRADING_SYMBOL,
(t.mtp-y.mtp)*100/y.mtp PER_LOSER
FROM  (SELECT INSTRUMENT_ID,TRADING_SYMBOL,TRADE_PRICE  mtp
FROM #temp_tick3a) t,
(SELECT INSTRUMENT_ID,TRADING_SYMBOL,CLOSE_PRICE  mtp
FROM STOCK_HISTORY
WHERE TRADE_DATE = prev_day) y
WHERE  t.INSTRUMENT_ID=y.INSTRUMENT_ID
) a
) b
where PER_LOSER < 0
ORDER BY PER_LOSER;

drop variable prev_day;
END