Generate 0 or Null values for data that doesn't exist

Question

I have some data that I am trying to represent using a graph with SSRS. Before I can generate this report, I am trying to account for data that does exist. I have setup a small data mart which aggregates daily totals. For some groups, data for a particular week does not exist. I am looking for a way to account for that data, so when I start to generate my reports, the line graphs represent the actual data, 0 for values that does exist. As an example:

yearnbr     weeknbr      restaurant     mealsserved
 2014        1           Joes Pasta     5
 2014        2           Joes Pasta     4
 2014        4           Joes Pasta     2

With the example above, the data for week three doesn't exist. I would like generate a query which could insert a 0 or null value for week 3. When I generate my report, the data for week 2 and 4 connect. I need to insert a 0 or Null for week 3. I am using SQL Server 2008 R2.

score 2 · Answer 1 · answered Jun 20 '14 at 18:51

You need to have a resultset to join to.

You can generate it on the fly:

WITH    weeks (w) AS
        (
        SELECT  1
        UNION ALL
        SELECT  w + 1
        FROM    weeks
        WHERE   w < 54
        )
SELECT  *
FROM    weeks
LEFT JOIN
        sales
ON      year = 2014
        AND weeknbr = w

but the performance would be quite poor.

It's better to create and populate a table with numbers (once):

CREATE TABLE num (n INT NOT NULL PRIMARY KEY)

WITH    q (n) AS
        (
        SELECT  1
        UNION ALL
        SELECT  n + 1
        FROM    q
        WHERE   n < 100000
        )
INSERT
INTO    num
SELECT  n
FROM    q

then use it in a query above:

SELECT  *
FROM    num
LEFT JOIN
        sales
ON      year = 2014
        AND weeknbr = n

score 1 · Answer 2 · answered Jun 20 '14 at 18:57

Test Data

DECLARE @TABLE TABLE 
  (
   yearnbr     INT,
   weeknbr     INT,
   restaurant  VARCHAR(20),
   mealsserved INT
   )
INSERT INTO @TABLE VALUES 
 (2014 ,1 ,'Joes Pasta',5),
 (2014 ,2 ,'Joes Pasta',4),
 (2014 ,4 ,'Joes Pasta',2)

Query

SELECT COALESCE(yearnbr,[Year]) AS yearnbr
      ,COALESCE(weeknbr,[Week]) AS weeknbr
      ,ISNULL(restaurant, '')   AS restaurant
      ,ISNULL(mealsserved, 0)   AS mealsserved 
FROM @TABLE t RIGHT JOIN 
                    (
                    VALUES (YEAR(GETDATE()), 1),
                           (YEAR(GETDATE()), 2),
                           (YEAR(GETDATE()), 3),
                           (YEAR(GETDATE()), 4)
                    ) A([Year],[Week])
ON t.yearnbr = A.[Year] AND t.weeknbr = A.[Week]

Result

╔═════════╦═════════╦════════════╦═════════════╗
║ yearnbr ║ weeknbr ║ restaurant ║ mealsserved ║
╠═════════╬═════════╬════════════╬═════════════╣
║    2014 ║       1 ║ Joes Pasta ║           5 ║
║    2014 ║       2 ║ Joes Pasta ║           4 ║
║    2014 ║       3 ║            ║           0 ║
║    2014 ║       4 ║ Joes Pasta ║           2 ║
╚═════════╩═════════╩════════════╩═════════════╝

score 0 · Answer 3 · answered Jun 20 '14 at 18:53

You can Join to a table of the week numbers. Here's one idea you can try:

Declare @Week Table (N TinyInt Not Null)
Insert  @Week Values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9) 

Select  *
From    Table   T
Right Join
(
    Select      (A.N + (10 * B.N))  Week
    From        @Week   A
    Cross Join  @Week   B
) W On T.weeknbr = W.Week
Where   W.Week Between 1 And 52
Order By W.Week

Generate 0 or Null values for data that doesn't exist

3 Answers3