Fill In Missing Dates for SQL Server Query Output

By: Jim Evans   |   Comments (2)   |   Related: More > Dates

Problem

When analyzing and reporting data occurrences over time with a roll up by a date part like week, day, hour, etc. often you will have gaps in your date values that will need to be accounted for.  For example, if no data exists for a certain date or hour, that date value will be missing from the output.  That missing value would technically have a value of 0 occurrences. 

The examples below show the contrasting results.

1) Datetime of error occurrences per hour, but missing rows where none occurred for 12:00 and 13:00!

2) Fills the date gap per hour where no errors occurred with a date place holder and 0 occurrences.

Solution

In the steps below, I will show one option for 'Filling the Date Gap'. First a Date table is needed to cover your date range.  Next, query the Date table with a LEFT JOIN to the source table.  Often you will find that you do not have a Date Table.  In this case you can use a Common Table Expression (CTE) query to generate a virtual Date Table. 

In the example I will share a Table Valued Function that uses a CTE to generate a virtual date table that can be used to join to. This example will use the data from the SQL ERRORLOG as test data.  I will use SQL system stored procedure sp_readerrorlog to load my test table.

Filling the Date Gap Demo for SQL Server

Step 1 –Set up the Virtual Date Common Table Expression (CTE)

We will use a Table Valued Function with common table expression (CTE) used to generate a list of numbers.  Then query the CTE adding a Date function to dynamically generate the dates.  The @PartofDate parameter is used to pass different date parts to generate a table at different date part levels including: year, month, week, day, hour, or minute.

Use YourDatabase;
Go
CREATE FUNCTION [dbo].[Get_DateList_uft] (
         @PartofDate as VARCHAR(10), --year, month, week, day, hour, minute
         @StartDate AS SMALLDATETIME,
         @EndDate  AS SMALLDATETIME = TIMESTAMP
 
)
   RETURNS TABLE AS RETURN
/******************************************************************************
*   SP Name: Get_DateList_uft
* File Name: Get_DateList_uft.sql
*   Created: 01/25/2007, Jim Evans
*      Desc: Returns a table on Date at the year, month, week, day, hour, or minute level.
*      Note: The number generator CTE came from a 2007 SQL Magazine article by Itzik Ben-Gan.
*  Modified:
*
* Example:
      SELECT DateList FROM dbo.Get_DateList_uft('DAY','20170101',NULL) AS DateListTbl;
      SELECT DateList FROM dbo.Get_DateList_uft('Hour','20170101',NULL) AS DateListTbl;
******************************************************************************/
--Convert time stamps to 00:00:00.000
WITH 
   LIST0 (Numbers) AS (SELECT 0 UNION ALL SELECT 0),                     --2 rows
   LIST1 (Numbers) AS (SELECT 0 FROM LIST0 A CROSS JOIN LIST0 B),        --4 rows
   LIST2 (Numbers) AS (SELECT 0 FROM LIST1 AS A CROSS JOIN LIST1 AS B),  --16 rows
   LIST3 (Numbers) AS (SELECT 0 FROM LIST2 AS A CROSS JOIN LIST2 AS B),  --256 rows
   LIST4 (Numbers) AS (SELECT 0 FROM LIST3 AS A CROSS JOIN LIST3 AS B),  --65536 rows
   LIST5 (Numbers) AS (SELECT 0 FROM LIST4 AS A CROSS JOIN LIST4 AS B)   --4294967296 rows
 
   SELECT @StartDate as 'DateList' --bring Start date back in because ROW_NUMBER() starts at 1!
   UNION ALL
   SELECT 
   TOP (CASE @PartofDate
         WHEN 'DAY' THEN DATEDIFF(DAY,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'HOUR' THEN DATEDIFF (HOUR,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'YEAR' THEN DATEDIFF (YEAR,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'MONTH' THEN DATEDIFF (MONTH,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'WEEK' THEN DATEDIFF (WEEK,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'MINUTE' THEN DATEDIFF (MINUTE,@StartDate,COALESCE(@EndDate,GETDATE()))
      END)
      (CASE @PartofDate
         WHEN 'DAY' THEN DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'HOUR' THEN DATEADD(HOUR,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'YEAR' THEN DATEADD(YEAR,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'MONTH' THEN DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'WEEK' THEN DATEADD(WEEK,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'MINUTE' THEN DATEADD(MINUTE,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
      END) AS DateList
   FROM LIST5
GO
 
--Test the Function call
SELECT DateList FROM dbo.Get_DateList_uft('DAY','20190101','20191231') AS DateListTbl;
SELECT DateList FROM dbo.Get_DateList_uft('Hour','2019-01-01 00:00:00',NULL) AS DateListTbl;
GO

Step 2 – Set up Sample Data

For this example, I'll use the SQL Errorlog for the sample data to show error log entries per hour.  I will use system stored proc sp_readerrorlog to gather the data to a temp table.

Create TABLE #Errors (
   LogDate     DATETIME,
   ProcessInfo NVARCHAR(50),    
   MessageText NVARCHAR(2000)
   )
insert #Errors
exec sp_readerrorlog
--exec sp_ReadErrorLog 0, 1, 'deadlock' --try this to see deadlocks per hour 
--exec sp_ReadErrorLog 0, 1, 'error'  --try this to see errors per hour
GO
--Inspect the Results
select * from #Errors

Step 3 – Show Results Without the Date Filler

Query the temp table used to store the SQL Error log data, counting error entries per hour.  GROUP BY the Logdate column from the temp table.  Here I use a combo date function to roll the Logdate values by HOUR.  This result set will have date gaps!

--Results with missing dates
SELECT DateAdd(Hour,DateDiff(Hour,'19000101',e.Logdate),'19000101') as [LogDate], 
   COUNT(e.Logdate) as [Occurrences]
FROM #Errors e 
GROUP BY DateAdd(Hour,DateDiff(Hour,'19000101',e.Logdate),'19000101')
ORDER BY [LogDate]
GO

This result has missing datetimes where no errors occurred.  16:00 and 17:00 hours are missing.

Step 4 – Show Results with Date Filler Table Valued Function

Query the temp table used to store the SQL Error log data.  Start with the Table valued function we created in Step 1: dbo.Get_DateList_uft.  Left Join the temp table used to store the SQL Error log data counting error entries per hour. This time GROUP BY the DateList column from the function dbo.Get_DateList_uft.  These results will fill the date gaps showing 0 Occurrences!

--Results with all dates
SELECT t.Datelist, COUNT(e.Logdate) as [Occurrences]
FROM dbo.Get_DateList_uft('Hour','2019-05-28 00:00:00',NULL) t
   LEFT JOIN #Errors e 
      ON t.Datelist = DateAdd(Hour,DateDiff(Hour,'19000101',e.Logdate),'19000101')
GROUP BY t.Datelist
ORDER BY t.Datelist 
GO

This result set includes the datetimes showing occurrences = 0, filling the date gap!

Wrap Up

In this demo I covered 'Filling the Date Gap', along with sharing a table valued function that can be used to generate a virtual date table.  Also, I shared examples of returning the results from a stored procedure to a temp table and the use of the combo functions DateAdd and DateDiff to roll up dates to the Hour.  I have used these techniques often over the years.

Additional Thoughts

• You may use this technique in business application when analyzing Calls per hour, Sales per day, Route stops per hour, Orders per day and many others.
• Often you may see the combo DateAdd, DateDiff functions with '19000101' represented as the numeric equivalent of 0 like DateAdd(Hour,DateDiff(Hour,0,GETDATE()),0).  The numeric value 0 gracefully converts to date '1900-01-01'.

Next Steps

• Read other date tips
• Create an Extended Date Dimension for a SQL Server Data Warehouse
• MS Creating a date dimension or calendar table in SQL Server

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

View all my tips