(Based on hist_qry5.sql.) Finds the points (specific days) when the 5-day moving average intersects the 21-day moving average for these stocks.
The results returned by this query are sorted by TRADING_SYMBOL and TRADE_DATE, and are similar to these rows. For example, the rows below are a sample of rows with a TRADE_DATE of 2012-06-04.
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.6463 | 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;