moving average over quartal - 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 2008 Forums

Transact-SQL (2008)

moving average over quartal

Author

Topic

rasta
Starting Member

23 Posts

Posted - 2011-12-05 : 07:51:45

I am trying to do the following using moving average. I have the following table and would like to calculate moving average of rate within time period of one year with the beginning every quartal. Only these values are count for the given quartal which date is situated between e.g. 1st January - 31st December for the first quartal.

1) 1st January 2007 - 31st December 2007
2) 1st April 2007 - 31th March 2008
3) 1st July 2007 - 30th June 2008
4) 1st October 2007 - 30th September 2008
.
.
.
x) 1st October 2010 - 30th September 2011

declare @t table (date datetime not null, rate float not null)

insert @t
select CAST('2007-12-31 00:00:00.000' as datetime), 1 union all
select CAST('2007-11-20 00:00:00.000' as datetime), 2 union all
select CAST('2007-5-04 00:00:00.000' as datetime), 4 union all
select CAST('2008-3-24 00:00:00.000' as datetime), 4 union all
select CAST('2008-01-01 00:00:00.000' as datetime), 5 union all
select CAST('2008-06-30 00:00:00.000' as datetime), 6 union all
select CAST('2009-07-18 00:00:00.000' as datetime), 6 union all
select CAST('2009-08-20 00:00:00.000' as datetime), 8 union all
select CAST('2010-12-31 00:00:00.000' as datetime), 9 union all
select CAST('2010-12-20 00:00:00.000' as datetime), 10 union all
select CAST('2011-01-01 00:00:00.000' as datetime), 11 union all
select CAST('2011-09-30 00:00:00.000' as datetime), 9

select * from @t

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 08:10:14

declare @t table (date datetime not null, rate float not null)

insert @t
select CAST('2007-12-31 00:00:00.000' as datetime), 1 union all
select CAST('2007-11-20 00:00:00.000' as datetime), 2 union all
select CAST('2007-5-04 00:00:00.000' as datetime), 4 union all
select CAST('2008-3-24 00:00:00.000' as datetime), 4 union all
select CAST('2008-01-01 00:00:00.000' as datetime), 5 union all
select CAST('2008-06-30 00:00:00.000' as datetime), 6 union all
select CAST('2009-07-18 00:00:00.000' as datetime), 6 union all
select CAST('2009-08-20 00:00:00.000' as datetime), 8 union all
select CAST('2010-12-31 00:00:00.000' as datetime), 9 union all
select CAST('2010-12-20 00:00:00.000' as datetime), 10 union all
select CAST('2011-01-01 00:00:00.000' as datetime), 11 union all
select CAST('2011-09-30 00:00:00.000' as datetime), 9

;with cte as
(
select dte = CONVERT(datetime,'20070101'), dte2 = CONVERT(datetime,'20071231')
union all
select dte = DATEADD(mm,3,dte), dte2 = DATEADD(mm,3,dte2) from cte where dte < '20101001'
)
select cte.dte, AVG(rate)
from @t t
join cte
on t.date between cte.dte and cte.dte2
group by cte.dte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rasta
Starting Member

23 Posts

Posted - 2011-12-05 : 12:18:33

It works great! Moreover, only a few lines of code are reqired. Thanks!

rasta
Starting Member

23 Posts

Posted - 2011-12-06 : 10:15:29

One more think. If there is a year having 366 days, such as the year of 2008, then the rest of the months occured after march 2008 will have 30 days.
This is problem for March and December.

;with cte as
(
select dte = CONVERT(datetime,'20070101'), dte2 = CONVERT(datetime,'20071231')
union all
select dte = DATEADD(mm,3,dte), dte2 = DATEADD(mm,3,dte2) from cte where dte < '20101001'
)
select cte.dte as quarter,cte.dte2 as outcome, count(*),
AVG(rate) from @t t
join cte
on t.date between cte.dte and cte.dte2
group by cte.dte,cte.dte2
order by dte

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources