Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

Calculating Moving Averages with T-SQL Expand / Collapse
Author
Message
Posted Monday, October 3, 2011 10:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 18, 2011 5:04 AM
Points: 26, 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 .
Post #1185007
Posted Tuesday, October 4, 2011 1:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1185052
Posted Tuesday, October 4, 2011 1:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 18, 2011 5:04 AM
Points: 26, Visits: 39
Yes
Post #1185054
Posted Tuesday, October 4, 2011 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 18, 2011 5:04 AM
Points: 26, Visits: 39
Sorry answer is NO
as on Holidays data is not available.
Post #1185061
Posted Thursday, October 6, 2011 2:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1186364
Posted Monday, February 27, 2012 3:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 7, 2012 10:32 AM
Points: 1, 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'.
Post #1258108
Posted Wednesday, August 8, 2012 4:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 29, 2013 9:23 AM
Points: 3, 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
Post #1342301
Posted Wednesday, August 8, 2012 5:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 35,593, Visits: 32,188
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1342317
Posted Thursday, August 9, 2012 5:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 29, 2013 9:23 AM
Points: 3, 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
Post #1342525
Posted Thursday, August 9, 2012 7:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:20 PM
Points: 235, Visits: 840
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.
Post #1342619
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse