tsql - GROUP BY WEEK with SQL

Question

I have the following:

http://sqlfiddle.com/#!6/226ae/1

I'm trying to now add one row for each week of the year, and filter the contacts accordingly. CONTACTS has a datetime column. The new table will look like:

        Status 1    Status 2    Status 3
Week 1      3          4           2
Week 2      1          5           3
Week 3      2          2           4

I think that DATEADD needs to be used, however I'm at a loss in terms of how to begin changing my query.

I do know that MySQL has a GROUP BY WEEK command, but I don't think that SQL has an equivalent. What's the best way to accomplish this?

Answer 1

You can use DATEPART(), this groups by both the week and the year in the event you have data spanning multiple years:

SELECT 
    'Week ' + cast(datepart(wk, created) as varchar(2)) Week,
    SUM(case WHEN status = 1 then 1 else 0 end) Status1,
    SUM(case WHEN status = 2 then 1 else 0 end) Status2,
    SUM(case WHEN status = 3 then 1 else 0 end) Status3,
    SUM(case WHEN status = 4 then 1 else 0 end) Status4,
    SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by datepart(wk, created), year(created)

See SQL Fiddle with Demo

Adding the year to the final result:

SELECT 
    'Week ' + cast(datepart(wk, created) as varchar(2)) Week,
    year(created) year,
    SUM(case WHEN status = 1 then 1 else 0 end) Status1,
    SUM(case WHEN status = 2 then 1 else 0 end) Status2,
    SUM(case WHEN status = 3 then 1 else 0 end) Status3,
    SUM(case WHEN status = 4 then 1 else 0 end) Status4,
    SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by  datepart(wk, created), year(created)

See SQL Fiddle with demo