Finding gaps or missing dates in a date range using TSQL

red_75116 used Ask the Experts™
I need to create a sql query very similar to the one listed in this article.

However,  instead of the results which are listed below, I need for the empno (001) to have a record for each date, even though the results for timein/timeout will be NULL.  When I alter this query to retrieve records for all employees, I will need to have a date record for each date for each employee like below, not the results in the article.
logdate empno timein timeout
---------- ----- ------ -------
05/18/2015 001 08:30 16:30
05/19/2015 001 NULL NULL
05/20/2015 001 09:00 15:30
05/21/2015 001 NULL NULL
05/22/2015 001 08:30 17:30
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

If you are doing this on a regular basis then you should seriously consider having a permanent "calendar table"

However it is possible to do this in many ways with or without such a table. The article uses a recursive CTE and while that approach works but isn't essential.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Here is an example without using "recursion" or a permanent calendar.
DECLARE @attendance TABLE
    ([logdate] date, [empno] varchar(3), [timein] time, [timeout] time)

INSERT INTO @attendance
    ([logdate], [empno], [timein], [timeout])
    ('2015-05-18 00:00:00', '001', '08:30', '16:30'),
    ('2015-05-20 00:00:00', '001', '09:00', '15:30'),
    ('2015-05-22 00:00:00', '001', '08:30', '17:30')

declare @from as date
declare @until as date
set @from = '20150501'
set @until = '20150601'

  cteDigits AS (
      SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS numbers(digit)
, cteTally AS (
            + [10s].digit * 10
            + [100s].digit * 100  /* add more like this as needed */
            --+ [1000s].digit * 1000  /* add more like this as needed */
            AS number
      FROM cteDigits [1s]
      CROSS JOIN cteDigits [10s]
      CROSS JOIN cteDigits [100s] /* add more like this as needed */
      --CROSS JOIN cteDigits [1000s] /* add more like this as needed */

     dateadd(day,d.number,@from) day, cj.empno, a.timein, a.timeout
from cteTally AS d
cross join (select distinct empno 
            from @attendance
            where logdate >= @from and logdate < @until
            ) AS cj
left join @attendance AS a on dateadd(day,d.number,@from) = a.logdate
                           and a.empno = cj.empno
where d.number <= datediff(day,@from,@until)
order by d.number

Open in new window


Thanks!  This was very helpful

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial