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 |
This is awesome, thank you!