Generate 0 or null values ​​for data that does not exist

advertisements

This question already has an answer here:

  • How to Determine Values for Missing Months based on Data of Previous Months in T-SQL 7 answers

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.


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