SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating Moving Averages with T-SQL


Calculating Moving Averages with T-SQL

Author
Message
sushilb
sushilb
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 39
Well the condition should not on 13 rows before , it should be on thirteenths days before
as below

select ID,
Symbol_Code,
Transaction_date,
Close_Price
from EOD_NSE_Stock
where
Transaction_date between
dateadd(day, -13, getdate()) and getdate()
and
Symbol_Code=@Symbol_Code


so there might be possibilty that i may miss some rows becoz of having holiday on some days.
thats why i m checking the the if else conditionto have exact 13 days data .
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16294 Visits: 19551
sushilb (10/3/2011)
Well the condition should not on 13 rows before , it should be on thirteenths days before
as below

select ID,
Symbol_Code,
Transaction_date,
Close_Price
from EOD_NSE_Stock
where
Transaction_date between
dateadd(day, -13, getdate()) and getdate()
and
Symbol_Code=@Symbol_Code


so there might be possibilty that i may miss some rows becoz of having holiday on some days.
thats why i m checking the the if else conditionto have exact 13 days data .


So missing days - holidays and weekends - are included in the 13 days?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
sushilb
sushilb
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 39
Yes
sushilb
sushilb
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 39
Sorry answer is NO
as on Holidays data is not available.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16294 Visits: 19551
Here's an interim solution for you. It's horribly inefficient however, I'm working on a second more efficient version.

DROP TABLE #ROCdata
CREATE TABLE #ROCdata (ID INT NOT NULL, Symbol_Code VARCHAR(12), Transaction_date DATE, Close_Price MONEY)
INSERT INTO #ROCdata (ID, Symbol_Code, Transaction_date, Close_Price)
SELECT 1, 'Company1', '28-Apr-10', 11045.27 UNION ALL --
SELECT 2, 'Company1', '29-Apr-10', 11167.32 UNION ALL --
SELECT 3, 'Company1', '30-Apr-10', 11008.61 UNION ALL --
SELECT 4, 'Company1', '3-May-10', 11151.83 UNION ALL --
SELECT 5, 'Company1', '4-May-10', 10926.77 UNION ALL --
SELECT 6, 'Company1', '5-May-10', 10868.12 UNION ALL --
SELECT 7, 'Company1', '6-May-10', 10520.32 UNION ALL --
SELECT 8, 'Company1', '7-May-10', 10380.43 UNION ALL --
SELECT 9, 'Company1', '10-May-10', 10785.14 UNION ALL --
SELECT 10, 'Company1', '11-May-10', 10748.26 UNION ALL --
SELECT 11, 'Company1', '12-May-10', 10896.91 UNION ALL --
SELECT 12, 'Company1', '13-May-10', 10782.95 UNION ALL --
SELECT 13, 'Company1', '14-May-10', 10620.16 UNION ALL -- -3.85
SELECT 14, 'Company1', '17-May-10', 10625.83 UNION ALL -- -4.85
SELECT 15, 'Company1', '18-May-10', 10510.95 UNION ALL -- -4.52
SELECT 16, 'Company1', '19-May-10', 10444.37 UNION ALL -- -6.34
SELECT 17, 'Company1', '20-May-10', 10068.01 UNION ALL -- -7.86
SELECT 18, 'Company1', '21-May-10', 10193.39 UNION ALL -- -6.21
SELECT 19, 'Company1', '24-May-10', 10066.57 UNION ALL -- -4.31
SELECT 20, 'Company1', '25-May-10', 10043.75 UNION ALL -- -3.24
SELECT 21, 'Company2', '28-Apr-10', 110535.27 UNION ALL --
SELECT 22, 'Company2', '29-Apr-10', 114167.32 UNION ALL --
SELECT 23, 'Company2', '30-Apr-10', 151008.61 UNION ALL --
SELECT 24, 'Company2', '3-May-10', 111551.83 UNION ALL --
SELECT 25, 'Company2', '4-May-10', 10926.77 UNION ALL --
SELECT 26, 'Company2', '5-May-10', 108568.12 UNION ALL --
SELECT 27, 'Company2', '6-May-10', 10520.32 UNION ALL --
SELECT 28, 'Company2', '7-May-10', 10380.43 UNION ALL --
SELECT 29, 'Company2', '10-May-10', 10785.14 UNION ALL --
SELECT 30, 'Company2', '11-May-10', 10748.26 UNION ALL --
SELECT 31, 'Company2', '12-May-10', 10896.91 UNION ALL --
SELECT 32, 'Company2', '13-May-10', 10782.95 UNION ALL --
SELECT 33, 'Company2', '14-May-10', 106420.16 UNION ALL -- -3.72
SELECT 34, 'Company2', '17-May-10', 10625.83 UNION ALL -- -90.69
SELECT 35, 'Company2', '18-May-10', 10510.95 UNION ALL -- -93.04
SELECT 36, 'Company2', '19-May-10', 10444.37 UNION ALL -- -90.64
SELECT 37, 'Company2', '20-May-10', 10068.01 UNION ALL -- -7.86
SELECT 38, 'Company2', '21-May-10', 10193.39 UNION ALL -- -90.61
SELECT 39, 'Company2', '24-May-10', 10066.57 UNION ALL -- -4.31
SELECT 40, 'Company2', '25-May-10', 10043.75 -- -3.24

-- calculate ROC
SELECT
d1.ID,
d1.Symbol_Code,
d1.Transaction_date,
d1.Close_Price,
DateToCompare = DATEADD(dd,-13,d1.Transaction_date),
d13.DateChosen,
ROC = (d1.Close_Price-d13.Close_Price)/(1.000*d13.Close_Price) * 100
FROM #ROCdata d1
OUTER APPLY (
SELECT TOP 1
DateChosen = Transaction_date,
Close_Price
FROM #ROCdata
WHERE Symbol_code = d1.Symbol_Code
AND Transaction_date <= DATEADD(dd,-13,d1.Transaction_date)
ORDER BY Transaction_date DESC) d13
ORDER BY d1.Symbol_Code, d1.Transaction_date



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
quartzier
quartzier
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 15
with t1 as (select row_number() over (order by quote_date) n
,quote_date
,close_price
from #google_stock)
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'.
stephen.poulitsis
stephen.poulitsis
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
Dear Gabriel
Your article is outstanding!
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86446 Visits: 41098
quartzier (2/27/2012)
with t1 as (select row_number() over (order by quote_date) n
,quote_date
,close_price
from #google_stock)
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stephen.poulitsis
stephen.poulitsis
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 32
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
Gabriel P
Gabriel P
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 Visits: 947
stephen.poulitsis (8/8/2012)
Dear Gabriel
Your article is outstanding!
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search