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

Answer 2

So, let's break this down:

You want to make a report that shows, for each contact, a breakdown, week by week, of the number of cases registered to that contact, which is divided into three columns, one for each StateCode.

If this is the case, then you would need to have 52 date records (or so) for each contact. For calendar like requests, it's always good to have a separate calendar table that lets you query from it. Dan Guzman has a blog entry that creates a useful calendar table which I'll use in the query.

WITH WeekNumbers AS
(
    SELECT
        FirstDateOfWeek,
         -- order by first date of week, grouping calendar year to produce week numbers
        WeekNumber = row_number() OVER (PARTITION BY CalendarYear ORDER BY FirstDateOfWeek)
    FROM
        master.dbo.Calendar -- created from script
    GROUP BY
        FirstDateOfWeek,
        CalendarYear
), Calendar AS
(
    SELECT
        WeekNumber =
        (
            SELECT
                WeekNumber
            FROM
                WeekNumbers WN
            WHERE
                C.FirstDateOfWeek = WN.FirstDateOfWeek
        ),
        *
    FROM
        master.dbo.Calendar C
    WHERE
        CalendarDate BETWEEN '1/1/2012' AND getutcdate()
)

SELECT
    C.FullName,
    ----include the below if the data is necessary
    --Cl.WeekNumber,
    --Cl.CalendarYear,
    --Cl.FirstDateOfWeek,
    --Cl.LastDateOfWeek,
    'Week: ' + CAST(Cl.WeekNumber AS VARCHAR(20))
    + ', Year: ' + CAST(Cl.CalendarYear AS VARCHAR(20)) WeekNumber
FROM
    CRM.dbo.Contact C
    -- use a cartesian join to produce a table list
    CROSS JOIN
        (
            SELECT
                DISTINCT WeekNumber,
                CalendarYear,
                FirstDateOfWeek,
                LastDateOfWeek
            FROM
                Calendar
        ) Cl
ORDER BY
    C.FullName,
    Cl.WeekNumber

This is different from the solution Ben linked to because Marc's query only returns weeks where there is a matching value, whereas you may or may not want to see even the weeks where there is no activity.

Once you have your core tables of contacts split out week by week as in the above (or altered for your specific time period), you can simply add a subquery for each StateCode to see the breakdown in columns as in the final query below.

WITH WeekNumbers AS
(
    SELECT
        FirstDateOfWeek,
        WeekNumber = row_number() OVER (PARTITION BY CalendarYear ORDER BY FirstDateOfWeek)
    FROM
        master.dbo.Calendar
    GROUP BY
        FirstDateOfWeek,
        CalendarYear
), Calendar AS
(
    SELECT
        WeekNumber =
        (
            SELECT
                WeekNumber
            FROM
                WeekNumbers WN
            WHERE
                C.FirstDateOfWeek = WN.FirstDateOfWeek
        ),
        *
    FROM
        master.dbo.Calendar C
    WHERE
        CalendarDate BETWEEN '1/1/2012' AND getutcdate()
)

SELECT
    C.FullName,
    --Cl.WeekNumber,
    --Cl.CalendarYear,
    --Cl.FirstDateOfWeek,
    --Cl.LastDateOfWeek,
    'Week: ' + CAST(Cl.WeekNumber AS VARCHAR(20)) +', Year: ' + CAST(Cl.CalendarYear AS VARCHAR(20)) WeekNumber,
    (
        SELECT
            count(*)
        FROM
            CRM.dbo.Incident I
            INNER JOIN CRM.dbo.StringMap SM ON
                I.StateCode = SM.AttributeValue
            INNER JOIN 
                (
                    SELECT
                        DISTINCT ME.Name,
                        ME.ObjectTypeCode
                    FROM
                        CRM.MetadataSchema.Entity ME
                ) E ON
                SM.ObjectTypeCode = E.ObjectTypeCode
        WHERE
            I.ModifiedOn >= Cl.FirstDateOfWeek 
            AND I.ModifiedOn < dateadd(day, 1, Cl.LastDateOfWeek)
            AND E.Name = 'incident'
            AND SM.AttributeName = 'statecode'
            AND SM.LangId = 1033
            AND I.CustomerId = C.ContactId
            AND SM.Value = 'Active'
    ) ActiveCases,
    (
        SELECT
            count(*)
        FROM
            CRM.dbo.Incident I
            INNER JOIN CRM.dbo.StringMap SM ON
                I.StateCode = SM.AttributeValue
            INNER JOIN 
                (
                    SELECT
                        DISTINCT ME.Name,
                        ME.ObjectTypeCode
                    FROM
                        CRM.MetadataSchema.Entity ME
                ) E ON
                SM.ObjectTypeCode = E.ObjectTypeCode
        WHERE
            I.ModifiedOn >= Cl.FirstDateOfWeek 
            AND I.ModifiedOn < dateadd(day, 1, Cl.LastDateOfWeek)
            AND E.Name = 'incident'
            AND SM.AttributeName = 'statecode'
            AND SM.LangId = 1033
            AND I.CustomerId = C.ContactId
            AND SM.Value = 'Resolved'
    ) ResolvedCases,
    (
        SELECT
            count(*)
        FROM
            CRM.dbo.Incident I
            INNER JOIN CRM.dbo.StringMap SM ON
                I.StateCode = SM.AttributeValue
            INNER JOIN 
                (
                    SELECT
                        DISTINCT ME.Name,
                        ME.ObjectTypeCode
                    FROM
                        CRM.MetadataSchema.Entity ME
                ) E ON
                SM.ObjectTypeCode = E.ObjectTypeCode
        WHERE
            I.ModifiedOn >= Cl.FirstDateOfWeek 
            AND I.ModifiedOn < dateadd(day, 1, Cl.LastDateOfWeek)
            AND E.Name = 'incident'
            AND SM.AttributeName = 'statecode'
            AND SM.LangId = 1033
            AND I.CustomerId = C.ContactId
            AND SM.Value = 'Canceled'
    ) CancelledCases
FROM
    CRM.dbo.Contact C
    CROSS JOIN
        (
            SELECT
                DISTINCT WeekNumber,
                CalendarYear,
                FirstDateOfWeek,
                LastDateOfWeek
            FROM
                Calendar
        ) Cl
ORDER BY
    C.FullName,
    Cl.WeekNumber