Thursday, March 09, 2006

T-SQL Group by Week query

SQL Server


Group by Week

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 )


to group by week and have the week start on Saturday:
DateAdd(day, ( -1 * datepart(dw, @startDate)) % 7 , @startDate )