hist_qry7.sql

STOCK_VALUE
289690.0039

BEGIN-- Determine the value of $100,000 now if 1 year ago it was invested
-- equally in 10 specified stocks (i.e. allocation for each stock is $10,000).
-- The trading strategy is: When the 20-day moving avg crosses over the
-- 5 month moving avg the complete allocation for that stock is invested
-- and when the 20-day moving avg crosses below the 5 month moving avg
-- the entire position is sold.  The trades happen on the closing price
-- of the trading day.

truncate table hist_temp;
truncate table hist7_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.INSTRUMENT_ID BETWEEN 11 and 20
and B.TRADE_DATE >= DATEADD(DAY,-160,'2012-06-01')
and B.TRADE_DATE <= '2012-12-01' 
GROUP BY B.INSTRUMENT_ID,B.TRADING_SYMBOL,
B.TRADE_DATE, B.CLOSE_PRICE
ORDER BY B.INSTRUMENT_ID,
B.TRADE_DATE;


Insert hist7_temp
SELECT number(),x.INSTRUMENT_ID, x.TRADE_DATE, avg_5mth , avg_21day
FROM (SELECT B.INSTRUMENT_ID, B.TRADE_DATE,
AVG(C.CLOSE_PRICE * B.SPLIT_FACTOR) avg_5mth
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 - 160 and b.row_nbr 
GROUP BY B.INSTRUMENT_ID,
B.TRADE_DATE) x,
(SELECT B.INSTRUMENT_ID, 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 
GROUP BY B.INSTRUMENT_ID,
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.INSTRUMENT_ID, z.TRADE_DATE, diff, td2, diff2, pre_diff into #hist7_temp
from (SELECT a.INSTRUMENT_ID, a.TRADE_DATE,  b.avg_21day - b.avg_5mth as pre_diff
from hist7_temp a, hist7_temp b
where a.INSTRUMENT_ID = b.INSTRUMENT_ID
and b.row_nbr = a.row_nbr - 1 
) x,

 (SELECT a.INSTRUMENT_ID, a.TRADE_DATE, b.TRADE_DATE as td2, 
b.avg_21day - b.avg_5mth as diff2
from hist7_temp a, hist7_temp b
where a.INSTRUMENT_ID = b.INSTRUMENT_ID
and b.row_nbr = a.row_nbr + 1 
) y,
(SELECT INSTRUMENT_ID, TRADE_DATE, avg_21day - avg_5mth as diff
from hist7_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 pre_diff*diff <=0
and NOT (pre_diff=0 and diff=0);

select sum(mp2.CLOSE_PRICE * (10000/mp1.CLOSE_PRICE)) as STOCK_VALUE
from #hist7_temp t7, STOCK_HISTORY mp1, STOCK_HISTORY mp2
where t7.INSTRUMENT_ID = mp1.INSTRUMENT_ID 
and t7.INSTRUMENT_ID = mp2.INSTRUMENT_ID 
and t7.TRADE_DATE = mp1.TRADE_DATE
and t7.td2 = mp2.TRADE_DATE;
END