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!
Date | Error Count |
---|---|
5/30/2019 10:00 | 10 |
5/30/2019 11:00 | 5 |
5/30/2019 14:00 | 13 |
2) Fills the date gap per hour where no errors occurred with a date place holder and 0 occurrences.
Date | Error Count |
---|---|
5/30/2019 10:00 | 10 |
5/30/2019 11:00 | 5 |
5/30/2019 12:00 | 0 |
5/30/2019 13:00 | 0 |
5/30/2019 14:00 | 13 |
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.
LogDate | Occurrences |
---|---|
2019-05-26 15:00:00.000 | 57 |
2019-05-26 18:00:00.000 | 31 |
2019-05-26 19:00:00.000 | 1 |
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!
LogDate | Occurrences |
---|---|
2019-05-26 15:00:00.000 | 57 |
2019-05-26 16:00:00.000 | 0 |
2019-05-26 17:00:00.000 | 0 |
2019-05-26 18:00:00.000 | 31 |
2019-05-26 19:00:00.000 | 1 |
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

View all my tips