SQL Query in CRM Report

Question

A "Case" in CRM has a field called "Status" with four options.

I'm trying to build a report in CRM that fills a table with every week of the year (each row is a different week), and then counts the number of cases that have each Status option (the columns would be each of the Status options).

The table would look like this

         Status 1    Status 2    Status 3
Week 1       3         55          4
Week 2       5         23          5
Week 3       14        11          33

So far I have the following:

SELECT 
    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 [DB].[dbo].[Contact]

Which gives me the following:

Status 1   Status 2   Status 3  
   2         43          53

Now I need to somehow split this into 52 rows for the past year and filter these results by date (columns in the Contact table). I'm a bit new to SQL queries and CRM - any help here would be much appreciated.

Here is a SQLFiddle with my progress and sample data: http://sqlfiddle.com/#!2/85b19/1

Answer 1

Sounds like you want to group by a range. The trick is to create a new field that represents each range (for you one per year) and group by that.

Since it also seems like you want an infinite range of dates, marc_s has a good summary for how to do the group by trick with dates in a generic way: SQL group by frequency within a date range