# Calculating Moving Averages with T-SQL

• with t1 as (select row_number() over (order by quote_date) n

,quote_date

,close_price

select a.n

,a.quote_date

,a.close_price

,CAST(null as decimal(8,2)) [sma]

--add the close_price from 20 row prior to this one

,CAST(b.close_price as decimal(8,2)) [20_day_old_close]

into #mod_goog_data

from t1 a

left join t1 b

on a.n - 20 = b.n

i keep having issue entering this into SQLserver... is this oracle statement? keep saying Msg 156, Level 15, State 1, Line 43

Incorrect syntax near the keyword 'as'.

• Dear Gabriel

I would like to ask how the code would need to be altered if instead of one security, the table contained data for many securities.

This is similar to the the bank balance problem that Jeff uses in his article, where he calculates the running total for each account (in this case the SMA for each security in the EMA problem).

Can you help?

Kind Regards

Stephen Poulitsis

• quartzier (2/27/2012)

with t1 as (select row_number() over (order by quote_date) n

,quote_date

,close_price

select a.n

,a.quote_date

,a.close_price

,CAST(null as decimal(8,2)) [sma]

--add the close_price from 20 row prior to this one

,CAST(b.close_price as decimal(8,2)) [20_day_old_close]

into #mod_goog_data

from t1 a

left join t1 b

on a.n - 20 = b.n

i keep having issue entering this into SQLserver... is this oracle statement? keep saying Msg 156, Level 15, State 1, Line 43

Incorrect syntax near the keyword 'as'.

I know it's and old post and you might not be around anymore but do you have a semi-colon after the last statement before the WITH?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Dear Gabriel

Following my previous post, below is my attempt to solve the following problem:

Calculating a single ema on a table of data that contains the closing price of approximately 15000 securities.

As a newbie, I have tried to use your code, but am experiencing the following problem.

The ema requires the calculation of the sma.

Therefore, if I'm calculating a 12period ema, I would first have to calculate the 12 period sma on the 13th day. This is achieved by:

---Capture the averages prior to the start of the EMA interval period

select @initial_sma = AVG(case

when n<@ema_intervals then tClose

else null

end

)

FROM zpData_mod

WHERE n<@ema_intervals

However, when there are multiple securities, the above code calculates the average for n<12 for ALL securities.

I have tried to solve this by grouping

---the grouping doesn't work

---group by instrumentid

GROUP BY instrumentid

Below, I have all the code:

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 DECIMAL(10,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_instrumentid int

declare @prev_ema decimal(9,4), @initial_sma decimal(9,4)

declare @anchor int

---Setup the default intervals

set @ema_intervals =12

set @k1 = 2/(1+@ema_intervals+.000)

set @prev_instrumentid=0

---Capture the averages prior to the start of the EMA interval period

select @initial_sma = AVG(case

when n<@ema_intervals then tClose

else null

end

)

FROM zpData_mod

WHERE n<@ema_intervals

---the grouping doesn't work

---group by instrumentid

GROUP BY instrumentid

---Carry over update statement

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 t1.tClose*@k1 +@initial_sma*(1-@k1)

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 N, tDate

,instrumentid

,tClose

,ema12

FROM zpData_mod

• stephen.poulitsis (8/8/2012)

Dear Gabriel

I would like to ask how the code would need to be altered if instead of one security, the table contained data for many securities.

This is similar to the the bank balance problem that Jeff uses in his article, where he calculates the running total for each account (in this case the SMA for each security in the EMA problem).

Can you help?

Kind Regards

Stephen Poulitsis

Hi Stephen,

Because of the iterative nature of the update statement in that each row depends on the row prior to it, there's not an apparent way that comes to mind on how to do this with multiple securities and still keep it all as one update statement.

If it's possible that you can access SQL Server 2012 for this business requirement, I would implore you to learn about the new features they have added to the windowing functions using ROWS/RANGE functionality should theoretically provide a better way to accomplish this. I haven't done any work with securities in a while however, so I cannot confirm it.

• 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

Viewing 6 posts - 76 through 80 (of 80 total)