Finding gaps or missing dates in a date range using TSQL

| 5 Comments

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:

finding missing dates in sql server

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)

 

 

Let’s insert some sample data

 

 

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.

 

 

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

 

 

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

 

 

And here is what we got using that calendar table:

 

 

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.

 

 

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

Here’s the result.

 

 

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.


Also published on Medium.

Author: Marlon Ribunal

I'm here to learn and share things about data and the technologies around data.