Finding gaps or missing dates in a date range using TSQL

| 5 Comments

Follow me on twitter: @MarlonRibunal

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)


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.

Author: Marlon Ribunal

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

5 Comments

  1. My apologies for taking so long to respond, Marlon. Thank you for taking my post correctly. I’m humbled by your good comments. Keep up the great work and keep writing!

  2. SET @StartDate = ‘2000-01-01’;

    Msg 530, Level 16, State 1, Line 64
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    🙁

    • Please read Jeff Moden’s article on the negative impact of recursive cte like my example above (check the link at the end of my post or find Jeff’s comment below).

      Please reconsider your options. Since you asked. Here’s the answer to that. But then again, please reconsider your options 😉

      You can increase the recursion by using the query hint MAXRECURSION at the end of your Select statement. Again, I am not advising on using recursive CTE in similar situation stated in my post.

      YOU’VE been warned!

      OPTION (MAXRECURSION n)

      So, you’ll do something like this:

      SELECT …
      FROM dbo.TimeSheet t
      RIGHT JOIN calendardates c
      ON t.logdate = c.date
      OPTION ( MAXRECURSION 1000 );

      Please don’t do it! But it’s there for you to use if you insist 😉

  3. You might want to avoid that particular style of rCTE even for the small stuff. Please see the following article for why.
    http://www.sqlservercentral.com/articles/T-SQL/74118/

Leave a Reply

Required fields are marked *.