interday_tick_qry2.sql

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

Output

The rows returned by this query are:

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

Note:

The output displayed in this section is derived by running the query with the large set of sample data provided in the Sybase RAP Samples component.

SQL Statements

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