Group By using Weeks

By Bill Graziano on 2 October 2000 | Tags: Queries , SELECT

Robert writes "I have a database that contains articles that are rated between 0 and 10. I want to show the average score for articles on a week-by-week basis. In other words, I want to output something like this:
Week commencing 27/1/00 Average Score = 3.7/10
Week commencing 3/2/00 Average Score = 5/10
...and so on...

In other situations, I would use GROUP BY but I don't see how I can do that with a range of dates. Can you help???"

I think we can make this one work. Please not that I'm going to use American date formats since that's easier for me. This will work equally well no matter what your date format. The first step is to convert all your dates to the first day of the week. Let's suppose your table looks like this:

Articles
---------------------
ReviewDate datetime
ReviewScore tinyint

I'll use the DATEPART function to strip out pieces of a date. For example,

select datepart(dw, '10/2/2000')

returns 2 since October 2nd is a Monday and the second day of the week. Now I can determine what day of the week each date falls on. I can subtract that from each date to convert them back to the first day of the week. That query looks something like this:

SELECT Week = DateAdd(day, -1 * datepart(dw, ReviewDate), ReviewDate )
FROM Articles

I'm using the DATEADD function to subtract the day of the week from each date. Now for the GROUP BY part. This is actually a little tricky. You can't group by WEEK since it is an alias for a function. What you can do is group by the actual function. Adding the average looks like this:

SELECT Week = DateAdd(day, -1 * datepart(dw, ReviewDate), ReviewDate ),
  Avg_Score = Avg(convert(float, ReviewScore))
FROM Articles
GROUP BY DateAdd(day, -1 * datepart(dw, ReviewDate), ReviewDate )

Remember that I have to convert ReviewScore to a floating point number since it is TINYINT in my table.

Also keep in mind that you can use the SET DATEFIRST to determine which day of the week is considered the first day of the week. Happy article reviewing :)