SQL

The following script contains the SQL statements for this query. Note that there are separate scripts optimized for the RAP cache and the RAP repository.

RAP repository

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.

-- This query is optimized to run on  IQ.

commit
;

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-11'
group by INSTRUMENT_ID) y
WHERE  st.TRADE_DATE =  '2005-11-11'
AND st.TRADE_TIME = maxtime and idx = st.INSTRUMENT_ID
;


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

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

RAP cache

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

-- This query is optimized to run on ASE.

set parallel_degree 1
go


-- current day
Select  INSTRUMENT_ID, TRADING_SYMBOL, TRADE_PRICE, TRADE_DATE into #temp_tick3a
FROM  STOCK_TRADE st, (Select INSTRUMENT_ID AS idx, max(TRADE_TIME) AS maxtime
from STOCK_TRADE 
where TRADE_TIME between '2005-11-11 00:00:00' and '2005-11-11 23:59:59'
group by INSTRUMENT_ID) y
-- WHERE  st.TRADE_DATE =  '2005-11-11'
WHERE  st.TRADE_TIME between '2005-11-11 00:00:00' and '2005-11-11 23:59:59'AND st.TRADE_TIME = maxtime and idx = st.INSTRUMENT_ID



set rowcount 10 
SELECT  TRADING_SYMBOL, PER_LOSER
FROM   (SELECT TRADING_SYMBOL, PER_LOSER 
FROM   (SELECT t.INSTRUMENT_ID, t.TRADING_SYMBOL, (t.mtp-y.mtp)*100/y.mtp PER_LOSER
FROM  (SELECT INSTRUMENT_ID,TRADING_SYMBOL, TRADE_DATE ,TRADE_PRICE  mtp
FROM #temp_tick3a) t,
(SELECT INSTRUMENT_ID, TRADING_SYMBOL, TRADE_DATE,TRADE_PRICE  mtp
FROM LAST_TRADE_PRICE) y
WHERE  t.INSTRUMENT_ID=y.INSTRUMENT_ID
AND    y.TRADE_DATE = '2005-11-10'
) a
) b
where PER_LOSER < 0
ORDER BY PER_LOSER ASC

set rowcount 0

drop table #temp_tick3a

go

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF