tick_qry6.sql

TRADING_SYMBOL          PER
ARG                     0.005943536404160475482912
ACV                     0.005161802660313678777049
BEY                     0.004888481515429269783073
AJE                     0.004440649017933390264730
AMX                     0.004440649017933390264730
AUM                     0.004429678848283499446290
AKO                     0.004239084357778719796523
AUN                     0.004237288135593220338983
BED                     0.004135338345864661654135
AJM                     0.004063134864822628535708

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

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