CODEWITHSUNDEEP
sqlsql-servert-sql
RobVious
RobVious

Reputation: 12915

SQL Grouping by predefined date range

I have the following:

http://sqlfiddle.com/#!2/fca3d/1

I'm trying to now add one row for each week in the year, and filter the contacts accordingly. 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. Any help would be much appreciated!

Upvotes: 2

Views: 196

Answers (1)

Vincent Savard
Vincent Savard

Reputation: 35907

Just add a GROUP BY clause :

SELECT 
    WEEK(created),
    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 WEEK(created), YEAR(created);

It's important to also group by year, otherwise it would group by week regardless of the year.
Also, no row is created if there's no data during a week. You'd need another set of data for this.

Edit: Here is an example for SQL Server 2012 [SQLFiddle]

Upvotes: 2

Related Questions