group by datepart(wk, date) creates partial weeks

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)

group by datepart(wk, date) creates partial weeks

Author

Topic

ehona
Starting Member

1 Post

Posted - 2008-02-08 : 11:07:41

Hello - I am grouping the sum of page requests and the average page wait time of an application by week and year. My problem is that using 'group by datepart(wk, date)' creates a row for the partial weeks (made of < 7 days) at the beginning and end of the year, so the sum of page requests for those two weeks is artificially low. How can I add the values of two rows together to make the value of page requests complete for a whole week each new year?

requests page wait year week
98395 1.867142 2007 51
92013 1.702857 2007 52
13906 1.725000 2007 53
61057 1.830000 2008 1
131461 2.290000 2008 2
115725 1.987142 2008 3

My query so far:
SELECT x.*
FROM (SELECT TOP 90
SUM(Requests) AS 'Weekly Page Requests',
AVG([Average Wait]) AS 'Weekly Average of Daily Average Pagewait',
Datepart(yy, date) as 'year', Datepart(wk, date) as 'week'
FROM PageWaits_Warehouse_Daily as pw WITH (NOLOCK)
GROUP BY Datepart(yy, date), Datepart(wk, date)
ORDER BY 'year' desc, 'week' desc) as x
ORDER BY 'year' asc, 'week' asc

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-08 : 12:51:26

Does this work?


	SELECT TOP 90
	SUM(Requests) AS 'Weekly Page Requests', 
	AVG([Average Wait]) AS 'Weekly Average of Daily Average Pagewait', 
	Datepart(yy, min([date])) as 'year',
	Datepart(wk, min([date])) as 'week'
	FROM PageWaits_Warehouse_Daily as pw WITH (NOLOCK)
	group by datediff(day, 0, [date]) / 7
	ORDER BY min([date]) desc

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources