hist_qry5.sql

Finds the 21-day and 5-day moving average price for a specified list of 1000 stocks during a 6-month period, using split-adjusted prices.

Output

The first 24 rows returned by this query are:

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

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:

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