SQL

The following script contains the SQL statements for this query:
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