hist_qry5.sql

TRADING_SYMBOL  TRADE_DATE      AVG_5DAY     AVG_21DAY
AAA             2010-06-01        381.72   383.7142857
AAB             2010-06-01        397.28    402.472381
AAC             2010-06-01       1452.32   1393.725714
AAD             2010-06-01   223.0616667   231.3666667
AAE             2010-06-01        481.05   482.5571429
AAF             2010-06-01   105.8666667   101.4019048
AAG             2010-06-01   288.1816667   285.0571429
AAH             2010-06-01       1743.07       1816.68
AAI             2010-06-01        105.43   103.2509524
AAJ             2010-06-01   487.2633333   463.6709524
AAK             2010-06-01   1081.116667   1035.864762
AAL             2010-06-01   326.8266667    317.287619
AAM             2010-06-01   688.7283333   682.9061905
AAN             2010-06-01       1246.46   1162.657143
AAO             2010-06-01         911.6         923.7
AAP             2010-06-01        700.46   696.4857143
AAQ             2010-06-01        320.64   319.6285714
AAR             2010-06-01   125.5466667   127.5790476
AAS             2010-06-01   2397.198333   2447.154762
AAT             2010-06-01   70.21666667   66.85952381
AAU             2010-06-01       1447.55   1429.114286
AAV             2010-06-01  199.2166667   328.3333333
AAW             2010-06-01       1921.27   1846.160952
AAX             2010-06-01       1792.05   1764.205238
...

-- Find the 21-day and 5-day moving average price for a specified
-- list of 1000 stocks during a 6-month period. (Use split adjusted prices) */
truncate table hist_temp;
commit;


insert hist_temp
SELECT number(),B.INSTRUMENT_ID, B.TRADING_SYMBOL,B.TRADE_DATE, B.CLOSE_PRICE,
IFNULL(sum(A.SPLIT_FACTOR),1,sum(A.SPLIT_FACTOR)) 
FROM STOCK_HISTORY AS B
left outer join SPLIT_EVENT as A
on B.INSTRUMENT_ID = A.INSTRUMENT_ID
AND B.TRADE_DATE < A.EFFECTIVE_DATE 
WHERE B.TRADING_SYMBOL BETWEEN 'AAA' AND 'BML'
AND LENGTH(B.TRADING_SYMBOL) = 3
and B.TRADE_DATE >= DATEADD(DAY,-28,'2010-06-01')
and B.TRADE_DATE <= '2010-12-01' 
GROUP BY B.INSTRUMENT_ID, B.TRADING_SYMBOL,
B.TRADE_DATE, B.CLOSE_PRICE
ORDER BY B.INSTRUMENT_ID,
B.TRADE_DATE;


SELECT x.TRADING_SYMBOL, x.TRADE_DATE, AVG_5DAY , AVG_21DAY
FROM (SELECT B.INSTRUMENT_ID, B.TRADING_SYMBOL, B.TRADE_DATE,
AVG(C.CLOSE_PRICE * B.SPLIT_FACTOR ) avg_5day
FROM hist_temp as B 
left outer join hist_temp as C
on B.INSTRUMENT_ID = C.INSTRUMENT_ID
and c.row_nbr BETWEEN b.row_nbr - 5 and b.row_nbr 
Where B.TRADE_DATE >= '2010-06-01'
GROUP BY B.INSTRUMENT_ID, B.TRADING_SYMBOL,
B.TRADE_DATE) x,
(SELECT B.INSTRUMENT_ID, B.TRADING_SYMBOL, B.TRADE_DATE,
AVG(C.CLOSE_PRICE * B.SPLIT_FACTOR ) avg_21day
FROM hist_temp as B 
left outer join hist_temp as C
on B.INSTRUMENT_ID = C.INSTRUMENT_ID
and c.row_nbr BETWEEN b.row_nbr - 21 and b.row_nbr 

Where B.TRADE_DATE >= '2010-06-01'
GROUP BY B.INSTRUMENT_ID, B.TRADING_SYMBOL,
B.TRADE_DATE) y 
where x.INSTRUMENT_ID = y.INSTRUMENT_ID
and x.TRADE_DATE = y.TRADE_DATE
;