Calculating Moving Averages with T-SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gabriel Priester » Calculating Moving Averages with T-SQL

81 posts, Page 9 of 9

«««5 6 7 8 9

Calculating Moving Averages with T-SQL

Rate Topic

Display Mode

Topic Options

Author

Message

stephen.poulitsis

Posted Saturday, August 11, 2012 3:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 29, 2013 9:23 AM
Points: 3, Visits: 32

Dear Gabriel
Admittedly, utilising copy-paste to the hilt, I combined your solution with the technique used by Jeff Moden in his article "Solving the Running Total and Ordinal Rank Problems" - and came up with the following solution, which seems to work correctly. If you ask me what parts of the code are actually doing, embarrassingly I probably wouldn't be able to say!
Anyway, here goes:

DROP TABLE zpData_mod

---=====Setup working table for EMAs
SELECT ROW_NUMBER() OVER (PARTITION BY InstrumentID ORDER BY tdate)n
,InstrumentID
,tDate
,tClose
,CAST (NULL AS numeric(12,5)) [tCloseRunSum]
,CAST (NULL AS INT) [InstrumentRunCount]
,CAST (NULL AS numeric(12,5)) [EMA12]
INTO zpData_mod
FROM zpData

CREATE CLUSTERED INDEX ix_n ON zpData_mod (InstrumentID, tdate)

---declare variables needed
declare @ema_intervals int, @k1 decimal(4,3)
declare @prev_ema numeric(12,5), @initial_sma numeric(12,5)
declare @anchor int
DECLARE @PrevInstrumentID INT
DECLARE @tCloseRunSum numeric(12,5)
DECLARE @InstrumentRunCount INT

---Setup the default intervals
set @ema_intervals =12
set @k1 = 2/(1+@ema_intervals+.000)

--====Update the running total and running count using for this row
--using the "Quirky Update" and a "Pseudo-Cursor".
--The order of the UPDATE is controlled by the clustered index
UPDATE zpData_mod
SET @tCloseRunSum = tCloseRunSum = CASE
WHEN InstrumentID = @PrevInstrumentID
THEN @tCloseRunSum + tClose
ELSE tClose
END,
@InstrumentRunCount = InstrumentRunCount = CASE
WHEN InstrumentID = @PrevInstrumentID
THEN @InstrumentRunCount + 1
ELSE 1
END,
@initial_sma = ema12 = case when
N=@ema_intervals
then @tCloseRunSum/@InstrumentRunCount
else
null
end,
@PrevInstrumentID = InstrumentID
FROM zpData_mod WITH (TABLOCKX)
OPTION (MAXDOP 1)

update t1
---case statement to handle @moving_sum variable
---depending on the value of n
set
@prev_ema=case when n<@ema_intervals then null
when n=@ema_intervals then tCloseRunSum/InstrumentRunCount
when n>@ema_intervals then t1.tClose*@k1 +@prev_ema*(1-@k1)
end,
ema12=@prev_ema,
@anchor =N ---anchor so that the carryover works
FROM zpData_mod t1 with (tablockx)
option (maxdop 1)

select *
FROM zpData_mod

It is truly a privelege to be able to communicate with you!
Thank you
Stephen

Post #1343771

« Prev Topic | Next Topic »

81 posts, Page 9 of 9

«««5 6 7 8 9

Permissions