Moving Average Total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Moving Average Total

Author

Topic

jay83091
Starting Member

14 Posts

Posted - 2010-07-01 : 23:32:07

Hi Experts,

I am trying to get the moving average for the past 12 months in a simple fact table. (i.e. July 2010 => averages of past 11 months + current month)

I have a period column (200912, 201001, 201002, 201003, etc) and a value coumn containing figures.

200912 / 10
200912 / 12
200912 / 13
201001 / 12
.
.
.
etc

End results that I want to achieve is like this
period / Sales / Moving_average
200912 / 120 / 118
201001 / 150 / 140
201002 / 200 / 150

etc

could you please help,.

thanks alot

James

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-02 : 00:47:57

you can use CROSS APPLY to calculate the avg


select *
from   [simple fact table] t
       cross apply
       (
           select Moving_average = avg(Sales)
           from   [simple fact table] x
           where  x.period >= convert(varchar(6), dateadd(month, -11, convert(datetime, convert(varchar(10), t.period) + '01')), 112)
           and    x.period <= t.period
       ) a

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-02 : 04:57:50

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources