Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculating Moving Averages with T-SQL Rate Topic Display Mode Topic Options
Author
 Message
 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 GabrielAdmittedly, 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 EMAsSELECT 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_modFROM zpDataCREATE CLUSTERED INDEX ix_n ON zpData_mod (InstrumentID, tdate)---declare variables neededdeclare @ema_intervals int, @k1 decimal(4,3)declare @prev_ema numeric(12,5), @initial_sma numeric(12,5)declare @anchor int DECLARE @PrevInstrumentID INTDECLARE @tCloseRunSum numeric(12,5)DECLARE @InstrumentRunCount INT---Setup the default intervalsset @ema_intervals =12set @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 indexUPDATE 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_modIt is truly a privelege to be able to communicate with you!Thank youStephen
Post #1343771

 Permissions