tick_qry3.sql

Determines the top 10 percentage losers for the specified date on the specified exchanges, sorted by percentage loss.

Required setup

To capture data required by tick_qry3.sql, run setup_tick_qry3_last_price.sql at the end of each trading day on the RAPCache database.

Loss is calculated as a percentage of the last trade price of the previous day.

Output

The rows returned by this query are:

INSTRUMENT_ID   TRADING_SYMBOL    PER_LOSER   LOSER_RANK
          443   ARB             -1.7355085             1
          620   AXW             -1.72143974            2
          173   AGR             -1.47387226            3
           99   ADV             -1.22580645            4
          863   BHF             -1.12903225            5
          805   BEZ             -1.03437785            6
          440   AQY             -1.0184937             7
          448   ARG             -0.91441111            8
          925   BJP             -0.90879584            9
          374   AOK             -0.79365079           10

RAPStore Database Script

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

RAPCache Database Script

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