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