SQL Server: Group records by Week

Quick code snippet to group a recordset by week.  In this case we are summing hours worked per week.

SELECT DATEPART(YEAR,myDateField) AS 'Year',
           DATEPART(wk,myDateField) AS 'Week #',
           MIN(DATEADD(wk, DATEDIFF(wk,0,myDateField), 0)) AS 'Week date',
           SUM(COALESCE([Hours],0)) AS 'Hours'
FROM myTable
GROUP BY DATEPART(YEAR,myDateField),DATEPART(wk,myDateField)
ORDER BY 1,2

Leave a Reply