Finding gaps or missing dates in a date range using TSQL

Here’s a quick how-to on returning temporal data set that includes missing dates. Suppose you are tasked to query an employee’s “time sheet”. You’d want to return not only the days he’s reported to work but also all the days that he missed.

The expected result would look something like this:

Actually, this was asked in the Facebook page of a SQL Server user group.

These are the answers that I gave. There is more than one way to skin a cat (no cats were harmed in the writing of this post).

These are only two of the many options. Feel free to add yours in the comment section.

Test Data

Let’s create our sample primary table (TimeSheet)

 

CREATE TABLE TimeSheet
(
logdate DATETIME NULL
,empno CHAR(3) NULL
,timein SMALLDATETIME NULL
,timeout SMALLDATETIME NULL
);

 

Let’s insert some sample data

 

INSERT INTO dbo.TimeSheet
( logdate, empno, timein, timeout )
VALUES ( '5/18/2015', '001', '08:30AM', '04:30PM' )
,( '5/20/2015', '001', '09:00AM', '03:30PM' )
,( '5/22/2015', '001', '08:30AM', '05:30PM' );

 

Now that we have our sample data let’s run a couple of scripts.

Using Calendar Table

One of the most convenient ways of finding missing dates in a date range is by using a Calendar table. There’s a ton of materials on this topic. Here’s one that tells you why you need a Calendar table.

For our demo purpose, lets create a simple calendar table that will contain our date range.

 

CREATE TABLE calendar ( date SMALLDATETIME );

 

Then, let’s populate that with a small sample of date range:

 

INSERT INTO dbo.calendar
( date )
VALUES ( '05/18/2015' )
,( '05/19/2015' )
,( '05/20/2015' )
,( '05/21/2015' )
,( '05/22/2015' );

 

Let’s use the calendar table to come up with the expected result:

 

SELECT CONVERT(VARCHAR(10), c.date, 101) AS logdate
,t.empno
,CONVERT(CHAR(5), t.timein, 108) AS timein
,CONVERT(CHAR(5), t.timeout, 108) AS timeout
FROM dbo.TimeSheet t
RIGHT OUTER JOIN dbo.calendar c
ON t.logdate = c.date;

 

And here is what we got using that calendar table:

 

logdate empno timein timeout
---------- ----- ------ -------
05/18/2015 001 08:30 16:30
05/19/2015 NULL NULL NULL
05/20/2015 001 09:00 15:30
05/21/2015 NULL NULL NULL
05/22/2015 001 08:30 17:30

(5 row(s) affected)

 

Using Common Table Expression (CTE)

Introduced in SQL Server 2005, Common Table Expression (CTE) has been a convenient “tool” for many SQL Developers. Basically, a CTE is a temporary result set. It is not stored as an object in SQL Server. One of the advantages of CTE’s is it can reference itself. How is that useful? Well, we can see that in our example.

 

DECLARE @startdate DATETIME
,@enddate DATETIME;

SET @startdate = '05/18/2015';
SET @enddate = '05/22/2015';
WITH calendardates
AS ( SELECT date = @startdate
UNION ALL
SELECT DATEADD(DAY, 1, date)
FROM calendardates
WHERE DATEADD(DAY, 1, date) <= @enddate
)
SELECT CONVERT(VARCHAR(10), c.date, 101) AS logdate
,t.empno
,CONVERT(CHAR(5), t.timein, 108) AS timein
,CONVERT(CHAR(5), t.timeout, 108) AS timeout
FROM dbo.TimeSheet t
RIGHT JOIN calendardates c
ON t.logdate = c.date;

 

You probably noticed that our CTE named “calendardates” is referenced in the FROM clause within the CTE statement.

Here’s the result.

 

logdate empno timein timeout
---------- ----- ------ -------
05/18/2015 001 08:30 16:30
05/19/2015 NULL NULL NULL
05/20/2015 001 09:00 15:30
05/21/2015 NULL NULL NULL
05/22/2015 001 08:30 17:30

(5 row(s) affected)

 

Update: Jeff Moden called my attention to this beautiful article he wrote about the negative impact of Recursive CTE’s, which is a must-read for anyone interested in Recursive CTE’s or rCTE’s as he called them. He’s that good at making up terms like that. Well, if you heard about “RBAR” (ree-bar) and “Tally Table”, he coined those terms.

Please read Jeff’s “Hidden RBAR: Counting with Recursive CTE’s” before considering using rCTE’s in your TSQL codes.