Fill Missing Dates without Loops

I recently had a need to display some data on a report that used data bars and sparklines in SQL Server Reporting Services 2008 R2.  The report displays data of a fixed period, such as two fiscal years, and sometime data is not available for every month.  It was important to the user to have missing data returned as zero values.

My solution was to add the missing data via the query rather than a calculation in the report, let the server do the work.  However, this query pulls data from many metrics in multiple categories.  Using a loop or cursor to find and fill all missing data points would be extraordinarily slow.  So, I used a set based solution.

I broke this challenge into two parts: 1- find all date/metric combinations, 2- join step 1’s result with the actual data we have for a final result.  The full query is available at the end of the article.

Step 1 – Find all date/metric combinations

I created two queries, one to find all dates in the date range:

SELECT  DateID
      , [Month]
      , [MonthName]
FROM    dbo.[Time]
WHERE   DateID BETWEEN @StartDate AND @EndDate

and another to find all metrics in the date range:

SELECT  MetricName
      , Value
      , Created
FROM    dbo.Metrics AS m
WHERE   Created BETWEEN @StartDate AND @EndDate

I then used a CROSS JOIN to combine them.  A GROUP BY eliminates the duplicates:

SELECT  t.[Month]
      , t.[MonthName]
      , m.[MetricName]
FROM    (SELECT DateID, [Month], [MonthName] FROM dbo.[Time] WHERE DateID BETWEEN @StartDate AND @EndDate) AS t
        CROSS JOIN (SELECT MetricName, Value, Created FROM dbo.Metrics AS m WHERE Created BETWEEN @StartDate AND @EndDate) AS m
GROUP BY t.[Month]
      , t.[MonthName]
      , m.[MetricName]

The whole thing is the definition of a CTE so I can easily use the query later:

; WITH    AllMetricsAllDates
          AS (
              SELECT    t.[Month]
                      , t.[MonthName]
                      , m.[MetricName]
              FROM      (SELECT DateID, [Month], [MonthName] FROM dbo.[Time] WHERE DateID BETWEEN @StartDate AND @EndDate) AS t
                        CROSS JOIN (SELECT MetricName, Value, Created FROM dbo.Metrics AS m WHERE Created BETWEEN @StartDate AND @EndDate) AS m
              GROUP BY  t.[Month]
                      , t.[MonthName]
                      , m.[MetricName])

Step 2 – Join up the Final Results

The final output is a LEFT JOIN of the all dates/all metrics CTE previously created with all the actual data.  Anything missing (NULL) is returned as a 0:

SELECT  a.[Month]
      , a.MetricName
      , Value = SUM(ISNULL(m.Value, 0))
FROM    AllMetricsAllDates AS a
        LEFT JOIN dbo.Metrics AS m ON a.Month = DATEADD(d, -1 * DAY(m.Created) + 1, m.Created)
                                      AND a.MetricName = m.MetricName
GROUP BY a.[Month]
      , a.MetricName

Full Query

Here’s the full query, it will create and fill test tables so you can see the whole thing run:

SET NOCOUNT ON
 
--	Create test tables	-----------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.Time', 'U') IS NOT NULL
    DROP TABLE dbo.[Time]
CREATE TABLE dbo.[Time] (
      DateID date NOT NULL
    , [Month] date NOT NULL
    , [MonthName] nvarchar(50) NOT NULL
    )
 
IF OBJECT_ID('dbo.Metrics', 'U') IS NOT NULL
    DROP TABLE dbo.Metrics
CREATE TABLE dbo.Metrics (
      ID int IDENTITY NOT NULL
    , MetricName nvarchar(50) NOT NULL
    , Value int NOT NULL
    , Created date NOT NULL
    )
 
--	Fill test tables with sample data	--------------------------------------------------------------------------------
DECLARE @EndDate date = '3/31/2011'
DECLARE @StartDate date = '1/1/2011'
DECLARE @CurrentDate date = @StartDate
 
WHILE @CurrentDate <= @EndDate
    BEGIN
        INSERT  INTO dbo.[Time]
                (DateID
               , [Month]
               , [MonthName])
        VALUES  (@CurrentDate
               , DATEADD(d, -1 * DAY(@CurrentDate) + 1, @CurrentDate)
               , DATENAME(mm, @CurrentDate))
 
        SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)
    END
 
-- Measure 1 in all 3 months, Measure 2 in most recent 2 months, Measure 3 in first two months	------------------------
INSERT  INTO dbo.Metrics (MetricName, Value, Created) VALUES (N'Measure1', 1, '1/16/2011')
INSERT  INTO dbo.Metrics (MetricName, Value, Created) VALUES (N'Measure1', 3, '2/13/2011')
INSERT  INTO dbo.Metrics (MetricName, Value, Created) VALUES (N'Measure1', 4, '3/25/2011')
INSERT  INTO dbo.Metrics (MetricName, Value, Created) VALUES (N'Measure2', 8, '2/2/2011')
INSERT  INTO dbo.Metrics (MetricName, Value, Created) VALUES (N'Measure2', 6, '3/15/2011')
INSERT  INTO dbo.Metrics (MetricName, Value, Created) VALUES (N'Measure3', 12, '1/28/2011')
INSERT  INTO dbo.Metrics (MetricName, Value, Created) VALUES (N'Measure3', 10, '2/5/2011')
 
--	Output query to fill all possible dates
; WITH    AllMetricsAllDates
          AS (-- CROSS JOIN combines all possible dates with all possible metrics
              SELECT    t.[Month]
                      , t.[MonthName]
                      , m.[MetricName]
              FROM      (SELECT DateID, [Month], [MonthName] FROM dbo.[Time] WHERE DateID BETWEEN @StartDate AND @EndDate) AS t
                        CROSS JOIN (SELECT MetricName, Value, Created FROM dbo.Metrics AS m WHERE Created BETWEEN @StartDate AND @EndDate) AS m
              GROUP BY  t.[Month]
                      , t.[MonthName]
                      , m.[MetricName])
 
-- final select combines the all possible date/metric combination from the CTE with any values that match the measure and date
SELECT  a.[Month]
      , a.MetricName
      , Value = SUM(ISNULL(m.Value, 0))
FROM    AllMetricsAllDates AS a
        LEFT JOIN dbo.Metrics AS m ON a.Month = DATEADD(d, -1 * DAY(m.Created) + 1, m.Created)
                                      AND a.MetricName = m.MetricName
GROUP BY a.[Month]
      , a.MetricName

One Response to Fill Missing Dates without Loops

  1. Oz says:

    This is awesome, thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *