Reporting on data when data is missing (ie. how to report zero activities for a customer on a given week)

Question

I want to create a report which aggregates the number of activities per customer per week. If there has been no activites on that customer for a given week, 0 should be displayed (i.e week 3 and 4 in the sample below)

CUSTOMER | #ACTIVITIES | WEEKNUMBER
A | 4 | 1
A | 2 | 2
A | 0 | 3
A | 0 | 4
A | 1 | 5
B ...
C ...

The problem is that if there are no activities there is no data to report on and therefor week 3 and 4 in the sample below is not in the report.

What is the "best" way to solve this?

Answer 1

Keep a table of time periods separately, and then outer left join the activities to it.

Like:

select *
from ReportingPeriod as p
left join Activities as a on a.ReportingPeriodId = p.ReportingPeriodId;

Answer 2

Try this:

DECLARE @YourTable table (CUSTOMER char(1), ACTIVITIES int, WEEKNUMBER int)
INSERT @YourTable VALUES ('A' , 4 , 1)
INSERT @YourTable VALUES ('A' , 2 , 2)
INSERT @YourTable VALUES ('A' , 0 , 3)
INSERT @YourTable VALUES ('A' , 0 , 4)
INSERT @YourTable VALUES ('A' , 1 , 5)
INSERT @YourTable VALUES ('B' , 5 , 3)
INSERT @YourTable VALUES ('C' , 2 , 4)


DECLARE @StartNumber int
       ,@EndNumber   int
SELECT @StartNumber=1
      ,@EndNumber=5

;WITH AllNumbers AS
(
    SELECT @StartNumber AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<@EndNumber
)
, AllCustomers AS
(
    SELECT DISTINCT CUSTOMER FROM @YourTable
)
SELECT
    n.Number AS WEEKNUMBER, c.CUSTOMER, CASE WHEN y.Customer IS NULL THEN 0 ELSE y.ACTIVITIES END AS ACTIVITIES
    FROM AllNumbers                   n
        CROSS JOIN AllCustomers       c
        LEFT OUTER JOIN @YourTable    y ON n.Number=y.WEEKNUMBER AND c.CUSTOMER=y.CUSTOMER

--OPTION (MAXRECURSION 500)

OUTPUT:

WEEKNUMBER  CUSTOMER ACTIVITIES
----------- -------- -----------
1           A        4
1           B        0
1           C        0
2           A        2
2           B        0
2           C        0
3           A        0
3           B        5
3           C        0
4           A        0
4           B        0
4           C        2
5           A        1
5           B        0
5           C        0

(15 row(s) affected)

I use a CTE to build a Numbers table, but you could build a permanent one look at this question: http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table. You could Write the Query without a CTE (same results as above):

SELECT
    n.Number AS WEEKNUMBER, c.CUSTOMER, CASE WHEN y.Customer IS NULL THEN 0 ELSE y.ACTIVITIES END AS ACTIVITIES
    FROM Numbers n
        CROSS JOIN (SELECT DISTINCT 
                        CUSTOMER 
                        FROM @YourTable
                   ) c
        LEFT OUTER JOIN @YourTable y ON n.Number=y.WEEKNUMBER AND c.CUSTOMER=y.CUSTOMER
    WHERE n.Number>=1 AND n.Number<=5
    ORDER BY n.Number,c.CUSTOMER