Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Fill In Missing Dates for SQL Server Query Output


By:   |   Last Updated: 2019-07-12   |   Comments   |   Related Tips: 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


Last Updated: 2019-07-12


get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager with Stericycle who has managed DBAs, BI Developer, and Data Management teams over the past 10 years.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools