Group By using WeeksBy 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 weekbyweek basis. In other words, I want to output something like this:
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 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 ) 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 ), 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 :)

 Advertisement  