hist_qry6.sql

(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 Output is sorted by TRADING_SYMBOL and trade date.

Output

The results returned by this query are similar to the following 25 rows:

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

Note:

The output displayed in this section is derived by running the query with the large set of sample data provided in the Sybase RAP Samples component.

SQL Statements

The SQL statements for this query are:

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