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

-- Find the 10 stocks with the highest percentage spreads.
-- Spread is the difference between the last ask-price and
-- the last bid-price.
-- Percentage spread is calculated as a percentage of the
-- bid-point price (average of ask and bid price).

-- This query is optimized to run on IQ.

commit
;
SELECT TOP 10 TRADING_SYMBOL, PER, RANK() OVER ( ORDER BY per DESC) AS PER_RANK
FROM   (SELECT a.INSTRUMENT_ID, a.TRADING_SYMBOL,(ap-bp)*2/(ap+bp) AS per
FROM   (SELECT INSTRUMENT_ID, TRADING_SYMBOL, BID_PRICE as bp
FROM STOCK_QUOTE st, (Select INSTRUMENT_ID AS idx, max(QUOTE_TIME) AS maxtime
from STOCK_QUOTE where QUOTE_DATE = '2005-11-14'
AND BID_PRICE IS NOT NULL
AND BID_PRICE <> 0 
group by INSTRUMENT_ID) y
WHERE st.QUOTE_DATE = '2005-11-14'
AND BID_PRICE IS NOT NULL
AND BID_PRICE <> 0 
			       AND st.QUOTE_TIME = maxtime and idx = st.INSTRUMENT_ID			                                  	 group by INSTRUMENT_ID,TRADING_SYMBOL, BID_PRICE) a,
(SELECT INSTRUMENT_ID,TRADING_SYMBOL, ASK_PRICE AS ap
FROM   STOCK_QUOTE st, (Select INSTRUMENT_ID AS idx, max(QUOTE_TIME) AS maxtime
from STOCK_QUOTE where QUOTE_DATE = '2005-11-14'
AND ASK_PRICE IS NOT NULL
AND ASK_PRICE <> 0 
group by INSTRUMENT_ID) x
WHERE  st.QUOTE_DATE = '2005-11-14'
AND ASK_PRICE IS NOT NULL
AND ASK_PRICE <> 0 
AND st.QUOTE_TIME = maxtime and idx = st.INSTRUMENT_ID                          	group by INSTRUMENT_ID, TRADING_SYMBOL, ASK_PRICE) b
WHERE  a.INSTRUMENT_ID=b.INSTRUMENT_ID
)c 
ORDER BY PER DESC
; 

RAP cache

-- Find the 10 stocks with the highest percentage spreads.
-- Spread is the difference between the last ask-price and
-- the last bid-price.
-- Percentage spread is calculated as a percentage of the
-- bid-point price (average of ask and bid price).

-- This query will run on either the ASE or IQ platform.

set  rowcount 10
go

SELECT a.TRADING_SYMBOL, (ap-bp)*2/(ap+bp) AS PER
FROM   (SELECT INSTRUMENT_ID,TRADING_SYMBOL, BID_PRICE as bp
FROM STOCK_QUOTE st, (Select INSTRUMENT_ID AS idx, max(QUOTE_TIME) AS maxtime
from STOCK_QUOTE 
		   where 
		   QUOTE_TIME between '2005-11-14 00:00:00'  and '2005-11-14 23:59:59'                    AND BID_PRICE IS NOT NULL
AND BID_PRICE <> 0 
group by INSTRUMENT_ID,
TRADING_SYMBOL) y
WHERE 	
QUOTE_TIME between '2005-11-14 00:00:00'  and '2005-11-14 23:59:59'
AND BID_PRICE IS NOT NULL
AND BID_PRICE <> 0 
		       AND st.QUOTE_TIME = maxtime and idx = st.INSTRUMENT_ID			                           	       group by INSTRUMENT_ID, TRADING_SYMBOL,BID_PRICE) a,
(SELECT INSTRUMENT_ID, TRADING_SYMBOL, ASK_PRICE AS ap
FROM   STOCK_QUOTE st, (Select INSTRUMENT_ID AS idx, max(QUOTE_TIME) AS maxtime
from STOCK_QUOTE 
		   where 
QUOTE_TIME between '2005-11-14 00:00:00' and '2005-11-14 23:59:59'
AND ASK_PRICE IS NOT NULL
AND ASK_PRICE <> 0 
group by INSTRUMENT_ID,
TRADING_SYMBOL) x
WHERE 
QUOTE_TIME between '2005-11-14 00:00:00' and '2005-11-14 23:59:59'
AND ASK_PRICE IS NOT NULL
AND ASK_PRICE <> 0 
AND st.QUOTE_TIME = maxtime and idx = st.INSTRUMENT_ID                                               group by INSTRUMENT_ID, TRADING_SYMBOL,ASK_PRICE) b
WHERE  a.INSTRUMENT_ID=b.INSTRUMENT_ID
ORDER BY PER DESC

go
set  rowcount 0
go

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF