Finding gaps or missing dates in a date range using TSQL

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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.
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
red_75116Author Commented:
Thanks!  This was very helpful
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.