Rolling Weeks - 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 Weeks

Author

Topic

jeffbond
Starting Member

16 Posts

Posted - 2009-01-14 : 16:29:39

Hi,

I have a query that returns 3 columns and several rows:

year, week, value

ex:
2008,52,10
2008,53,7
2009,1,99
2009,2,3

I need a fourth column that would return the average of the sum of the 3 past weeks....

ex: last row in the example above would be: 2009,2,3,38.66

(10+7+99)/3 = 38.66

here is my sql so far:

select convert(char(4), td.date, 120) as year, datepart("ww",td.date) as weekN, count(td.value) as value
from mytable td

where
td.date >= @startdate
and td.date < @enddate
group by convert(char(4), td.date, 120), datepart("ww",td.date)

order by year asc, weekN asc

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-14 : 16:42:02

Have a look at this,
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117395&SearchTerms=average

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-01-14 : 17:12:47

Here's one method. On records that have < 3 prior records it will go back as far as it can.


Declare @T table ([year] int,[week] int,[value] int)

Insert Into @T
select 2008,52,10 Union all
select 2008,53,7 Union all
select 2009,1,99 Union all
select 2009,2,3 Union all
select 2009,6,1 Union all
select 2008,22,10


Select *,
coalesce((Select Avg(aa.[Value]) as [Value]
from 
	(	

		Select Row_Number() over (Order by aaa.[Year] DESC,aaa.[Week] DESC) as ROWID,convert(decimal(10,4),aaa.[Value]) as [value]
		from
		@T aaa
		where
		convert(decimal(10,0),convert(varchar(4),aaa.[year]) + ISNULL(REPLICATE('0', 2 - len(ISNULL(convert(varchar(2),aaa.[week]) ,0))), '') + convert(varchar(2),aaa.[week])) < convert(decimal(10,0),convert(varchar(4),a.[year]) + ISNULL(REPLICATE('0', 2 - len(ISNULL(convert(varchar(2),a.[week]) ,0))), '') + convert(varchar(2),a.[week]))
					) aa
					where aa.ROWID <= 3),0) as Average
from 
@T a
order by a.[Year],a.[Week]

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