Average values over the last 12 months

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 2008 Forums

Transact-SQL (2008)

Average values over the last 12 months

Author

Topic

skillilea
Starting Member

15 Posts

Posted - 2012-03-29 : 13:01:51

I am trying to create a return set with the monthly value and and average of the last 3 month rolling.

Looks like this.

Date MonValue 3MonthBackValue

Values are stored like this.

3/1/2011 10
4/1/2011 11
5/1/2011 8
6/1/2011 15
3/1/2011 12
4/1/2011 14
5/1/2011 12
6/1/2011 12

SO the ressult would be:

Date MonValue 3MonthBackValue
3/1/2011 10 AVG(10, 11, 8)
4/1/2011 11 AVG(11, 8, 15)
...

ANY WAY TO DO THIS IN A PIVOT OR A CTE.

I am passing in a start date and going 12 months back for the graph to start with.

Thanks tons.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 13:21:48

Wouldn't 3/1 be 10,11,8, 12,14,12???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 13:22:08

[code]

CREATE TABLE #t (myDate date, myValue int)
GO

INSERT INTO #t(myDate, myValue)
SELECT '3/1/2011', 10 UNION ALL
SELECT '4/1/2011', 11 UNION ALL
SELECT '5/1/2011', 8 UNION ALL
SELECT '6/1/2011', 15 UNION ALL
SELECT '3/1/2011', 12 UNION ALL
SELECT '4/1/2011', 14 UNION ALL
SELECT '5/1/2011', 12 UNION ALL
SELECT '6/1/2011', 12
GO

SELECT DISTINCT YEAR(l.myDate), MONTH(l.myDate), YEAR(DATEADD(mm,2,l.myDate)), MONTH(DATEADD(mm,2,l.myDate)), r.myValue
FROM #t l
JOIN #t r
ON DATEDIFF(mm,l.myDate,r.myDate) < 3
ORDER BY 1,2
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-03-29 : 14:35:44

Do not use this in a live environment, it is just a sample.

Steps are
1: order the table
2: group
3: query


drop table #t,#t1,#t2
CREATE TABLE #t (myDate datetime, myValue decimal)

INSERT INTO #t(myDate, myValue)
SELECT '3/1/2011', 10 UNION ALL
SELECT '4/1/2011', 11 UNION ALL
SELECT '5/1/2011',  8 UNION ALL
SELECT '6/1/2011', 15 UNION ALL
SELECT '3/1/2011', 12 UNION ALL
SELECT '4/1/2011', 14 UNION ALL
SELECT '5/1/2011', 12 UNION ALL
SELECT '6/1/2011', 12


select row_number() over (order by (select 1))as Rowid,* 
into #t1
from #t

select row_number() over (partition by month(mydate) order by rowid) as rowidgroup,*
into #t2
from #t1


select a.rowid,a.rowidgroup,a.mydate,avg(b.myvalue)
--select *
from #t2 a
cross apply
(select * from #t2 aa where aa.mydate between a.mydate and dateadd(month,2,aa.mydate) and aa.rowid <= a.rowid + 2 and aa.rowidgroup = a.rowidgroup)
b --order by a.rowidgroup,a.mydate
group by a.rowid,a.rowidgroup,a.mydate
order by rowidgroup,mydate
/*
rowid	rowidgroup	mydate	(No column name)
1	1	2011-03-01 00:00:00.000	9.666666
2	1	2011-04-01 00:00:00.000	11.333333
3	1	2011-05-01 00:00:00.000	11.500000
4	1	2011-06-01 00:00:00.000	15.000000
5	2	2011-03-01 00:00:00.000	12.666666
6	2	2011-04-01 00:00:00.000	12.666666
7	2	2011-05-01 00:00:00.000	12.000000
8	2	2011-06-01 00:00:00.000	12.000000*/

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources