1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| CREATE TABLE MovingAverage
(
TradeDateAvg SMALLdatetime,
[OpeningAvg] FLOAT,
B1MAvg2 FLOAT,
B1MAvg3 FLOAT,
B1MAvg4 FLOAT
);
;WITH MA AS
(
SELECT ma.TradeDate, ma.[Opening], ROW_NUMBER() OVER(ORDER BY TradeDate) AS [Order] FROM EurostoxxBase1 ma
)
INSERT INTO MovingAverage(TradeDateAvg, OpeningAvg, B1MAvg2, B1MAvg3, B1MAvg4)
SELECT
ma.[TradeDate],
ma.[Opening],
((ma.[Opening] + ma2.[Opening]) /2 ) AS B1MAvg2,
((ma.[Opening] + ma2.[Opening] + ma3.[Opening]) / 3) AS B1MAvg3,
((ma.[Opening] + ma2.[Opening] + ma3.[Opening] + ma4.[Opening]) / 4) AS B1Mavg4
FROM MA ma LEFT OUTER JOIN MA ma2 ON ma.[Order] = ma2.[Order] + 1
LEFT OUTER JOIN MA ma3 ON ma2.[Order] = ma3.[Order] + 1
LEFT OUTER JOIN MA ma4 ON ma3.[Order] = ma4.[Order] + 1 |
Partager