13

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?

RobVious
  • 12,685
  • 25
  • 99
  • 181

3 Answers3

39

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

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • From MSDN I was using the DATEDIFF method which does not support SET DATEFIRST 1; This answer supports first day of weeks corrrectly. – 27k1 Mar 16 '14 at 10:06
2

You can use the datepart function to extract the week from a date.

The query becomes:

SELECT datepart(week, created) as 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(week, created)

SqlFiddle: http://sqlfiddle.com/#!6/226ae/6tsq

Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
0

You might try using the group by clause in your query:

SELECT
    DATE_FORMAT( created, '%u' ) week_number,
    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 DATE_FORMAT( created, '%u' )

I'm assuming you are talking about mysql.

The DATE_FORMAT function is documented here:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

phanophite
  • 21
  • 6