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 @InstrumentRunCountINT

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