hist_qry6.sql

TRADING_SYMBOL  TRADE_DATE      DAY_5     DAY_21   PREV_DAY5   PREV_DAY21
AAG             2012-06-04    71.6416    71.7977     71.8916      71.8238
ABI             2012-06-04   303.3733   301.6436    300.4533     301.4476
ABX             2012-06-04     1264.2  1260.7418     1258.16    1259.9085
ACD             2012-06-04      171.7   170.2854      169.16     169.8628
ACP             2012-06-04   103.3083   103.0081    102.2816       102.79
AGT             2012-06-04     855.95    889.129      952.56     915.4057
AHS             2012-06-04     1156.5  1157.2772   1158.6666    1157.4333
AIC             2012-06-04    412.875   412.1018      410.85     411.6085
AJE             2012-06-04   140.5466   140.5545    141.2666     140.7352
AKA             2012-06-04      41.64    41.5381       41.44       41.478
AKU             2012-06-04    804.225   801.3395       798.9     800.4385
ALE             2012-06-04   138.6116   137.6359     136.535     137.2133
ALO             2012-06-04     484.44   483.0109    479.6133     481.9047
AML             2012-06-04     272.68   272.4145    271.3333     272.3161
AOC             2012-06-04   519.5166   543.8159     579.775     559.9976
APZ             2012-06-04   272.6916   272.2113    270.4333     271.6261
AQQ             2012-06-04  1034.8333  1031.1181   1026.4333    1030.2333
AQT             2012-06-04     126.89     125.79      125.21     125.4542
ARD             2012-06-04  1074.6266    1072.01   1067.5866    1070.8709
ARU             2012-06-04   1013.475  1063.6445     1136.64    1095.3428
ASA             2012-06-04      406.4   405.4436    405.1466     405.3485
ASH             2012-06-04     310.75    309.499      306.65     308.8228
ATF             2012-06-04   550.7866    550.269      549.92     550.3809
ATK             2012-06-04   136.2716     137.43    137.6833     137.5733
ATQ             2012-06-04    42.1333    42.3045       42.35       42.319
...

--(Based on the previous query) Find the points (specific days) when the
--5-day moving average intersects the 21-day moving average for these stocks.
--The output is to be sorted by INSTRUMENT_ID and date.

truncate table hist_temp;
truncate table hist6_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,'2012-06-01')
and B.TRADE_DATE <= '2012-12-01'
GROUP BY B.INSTRUMENT_ID,TRADING_SYMBOL,
B.TRADE_DATE, B.CLOSE_PRICE
ORDER BY B.INSTRUMENT_ID,
B.TRADE_DATE;

Insert hist6_temp
SELECT number(),x.INSTRUMENT_ID, 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 >= '2012-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 >= '2012-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
order by x.INSTRUMENT_ID, x.TRADE_DATE;


select z.TRADING_SYMBOL, z.TRADE_DATE, DAY_5, DAY_21, PREV_DAY5, PREV_DAY21
from (SELECT a.INSTRUMENT_ID, a.TRADING_SYMBOL, a.TRADE_DATE,  avg(b.avg_21day) as prev_day21
from hist6_temp a, hist6_temp b
where a.INSTRUMENT_ID = b.INSTRUMENT_ID
and b.row_nbr between a.row_nbr - 2 and a.row_nbr - 1
group by a.INSTRUMENT_ID, a.TRADING_SYMBOL, a.TRADE_DATE) x,
(SELECT a.INSTRUMENT_ID, a.TRADING_SYMBOL, a.TRADE_DATE,  avg(b.avg_5day) as prev_day5
from hist6_temp a, hist6_temp b
where a.INSTRUMENT_ID = b.INSTRUMENT_ID
and b.row_nbr between a.row_nbr - 2 and a.row_nbr - 1
group by a.INSTRUMENT_ID, a.TRADING_SYMBOL, a.TRADE_DATE) y,
(SELECT INSTRUMENT_ID, TRADING_SYMBOL, TRADE_DATE, avg_5day as day_5, avg_21day as day_21
from hist6_temp) z
where z.INSTRUMENT_ID = x.INSTRUMENT_ID
and z.TRADE_DATE = x.TRADE_DATE
and z.INSTRUMENT_ID = y.INSTRUMENT_ID
and z.TRADE_DATE = y.TRADE_DATE
and sign(day_21-day_5) * sign(prev_day21-prev_day5) < 0;