Rolling Average - 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)

Rolling Average

Author

Topic

mayaaa
Starting Member

15 Posts

Posted - 2009-03-24 : 10:53:54

Hey all :)

I need to calculate a rolling average per months.
it means, I need to always calculate average for 6 months earlier.
If I have this table:
months X Rolling Average
01-08 45 (45+0+0+0+0+0)/6
02-08 32 (32+45+0+0+0+0)/6
03-08 12 (12+32+45+0+0+0)/6
04-08 16 (16+12+32+45+0+0)/6
05-08 25 (25+16+12+32+45+0)/6
06-08 98 (98+25+16+12+32+45)/6
07-08 87 (87+98+25+16+12+32)/6
09-08 56 (56+87+98+25+16+12)/6
10-08 47 (47+56+87+98+25+16)/6
11-08 35 (35+47+56+87+98+25)/6
12-08 61 (61+35+47+56+87+98)/6

Is there any built-in function who does that in Sql Server 2005?

Please assist
Thank you!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-24 : 11:10:15

>>Is there any built-in function who does that in Sql Server 2005?
no, but it is pretty simple to do your own.

what is the datatype of your [months] column?
Do you have multiple rows for each month or just one row per month in your base data?

Be One with the Optimizer
TG

mayaaa
Starting Member

15 Posts

Posted - 2009-03-24 : 11:17:26

Hey TG :)
Thanks for your reply!
The months field is datetime.
X is decimal.
one row per month, exactly like in my example.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-24 : 11:19:02

another question. Since the first row (10/08) has no earlier rows are you sure you want 45/6 rather than 45/1?
Same for 02-08: (32+45)/2
etc.?

Be One with the Optimizer
TG

mayaaa
Starting Member

15 Posts

Posted - 2009-03-24 : 11:22:55

Yes, it makes more sense, of course

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-24 : 11:23:50

Here is one way:


set dateformat 'mdy'
declare @t table (dt datetime, x int)
insert @t
select '01/08/2008', 45 union all
select '02/08/2008', 32 union all
select '03/08/2008', 12 union all
select '04/08/2008', 16 union all
select '05/08/2008', 25 union all
select '06/08/2008', 98 union all
select '07/08/2008', 87 union all
select '09/08/2008', 56 union all
select '10/08/2008', 47 union all
select '11/08/2008', 35 union all
select '12/08/2008', 61 
print 'This gives you a typical rolling avg based on available data'
select t.dt
       ,t.x
       ,ca.ravg
from @t t
cross apply (
       select avg(x) ravg      
       from   @t z
       where  datediff(month, z.dt, t.dt) between 0 and 6
       ) ca

print 'This will give you your posted desired result'
select t.dt
       ,t.x
       ,ca.ravg
from @t t
cross apply (
       select sum(x)/6 ravg
       from   @t z
       where  datediff(month, z.dt, t.dt) between 0 and 6
       ) ca

output:
This gives you a typical rolling avg based on available data
dt                      x           ravg
----------------------- ----------- -----------
2008-01-08 00:00:00.000 45          45
2008-02-08 00:00:00.000 32          38
2008-03-08 00:00:00.000 12          29
2008-04-08 00:00:00.000 16          26
2008-05-08 00:00:00.000 25          26
2008-06-08 00:00:00.000 98          38
2008-07-08 00:00:00.000 87          45
2008-09-08 00:00:00.000 56          49
2008-10-08 00:00:00.000 47          54
2008-11-08 00:00:00.000 35          58
2008-12-08 00:00:00.000 61          64

This will give you your posted desired result
dt                      x           ravg
----------------------- ----------- -----------
2008-01-08 00:00:00.000 45          7
2008-02-08 00:00:00.000 32          12
2008-03-08 00:00:00.000 12          14
2008-04-08 00:00:00.000 16          17
2008-05-08 00:00:00.000 25          21
2008-06-08 00:00:00.000 98          38
2008-07-08 00:00:00.000 87          52
2008-09-08 00:00:00.000 56          49
2008-10-08 00:00:00.000 47          54
2008-11-08 00:00:00.000 35          58
2008-12-08 00:00:00.000 61          64

Be One with the Optimizer
TG

mayaaa
Starting Member

15 Posts

Posted - 2009-03-24 : 11:40:44

TG! many many thanks!
I'm going to check this out.
I will update you.
Have a great day! :)

mayaaa
Starting Member

15 Posts

Posted - 2009-03-25 : 04:52:55

Hey TG,

It works partially..
those are the dates i have:
2008-06-01 00:00:00.000 works
2008-07-01 00:00:00.000 works
2008-08-01 00:00:00.000 works
2008-09-01 00:00:00.000 works
2008-10-01 00:00:00.000 works
2008-11-01 00:00:00.000 works
2008-12-01 00:00:00.000 DOESN'T work
2009-01-01 00:00:00.000 DOESN'T work

Could you please assist?
Thank you so much!

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-25 : 05:31:23

Try this


set dateformat 'mdy'

declare @t table (dt datetime, x decimal(10,2))
insert @t
select '01/01/2008', 45 union all
select '02/01/2008', 32 union all
select '03/01/2008', 12 union all
select '04/01/2008', 16 union all
select '05/01/2008', 25 union all
select '06/01/2008', 98 union all
select '07/01/2008', 87 union all
select '09/01/2008', 56 union all
select '10/01/2008', 47 union all
select '11/01/2008', 35 union all
select '12/01/2008', 61


SELECT A.DT, A.X, AVG(b.X)
FROM @t A JOIN @t B
ON a.dt>=b.dt AND dateadd(month,-6,a.dt)<=b.dt
GROUP BY A.DT, A.X
ORDER BY 1

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mayaaa
Starting Member

15 Posts

Posted - 2009-03-25 : 05:55:28

Hey Mangal,
It's not working.. i get the same results as before.
I can give you my data to test on if you like?
Thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-25 : 05:56:02

[code]DECLARE @Sample TABLE
(
theMonth DATETIME,
theValue INT
)

INSERT @Sample
SELECT '2008-01-01', 45 UNION ALL
SELECT '2008-02-01', 32 UNION ALL
SELECT '2008-03-01', 12 UNION ALL
SELECT '2008-04-01', 16 UNION ALL
SELECT '2008-05-01', 25 UNION ALL
SELECT '2008-06-01', 98 UNION ALL
SELECT '2008-07-01', 87 UNION ALL
SELECT '2008-09-01', 56 UNION ALL
SELECT '2008-10-01', 47 UNION ALL
SELECT '2008-11-01', 35 UNION ALL
SELECT '2008-12-01', 61

SELECT s.theMonth,
s.theValue,
f.a
FROM @Sample AS s
CROSS APPLY (
SELECT AVG(1.0E * x.theValue)
FROM @Sample AS x
WHERE x.theMonth BETWEEN DATEADD(MONTH, -5, s.theMonth) AND s.theMonth
) AS f(a)
ORDER BY s.theMonth[/code]

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

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-25 : 06:15:27

Hi mayaaa,
I added some rows, and it is calculating correctly.
Yes please provide some sample data which is not giving correct result.

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mayaaa
Starting Member

15 Posts

Posted - 2009-03-25 : 06:29:35

Hey Peso & Mangal :)
thanks for the help, seems like Pesos solution is working great!!

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-25 : 07:17:35

Oh i guess it is dateadd(month,-5,a.dt) and not dateadd(month,-6,a.dt).

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources