Avatar of red_75116
red_75116
Flag for United States of America asked on

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.  

http://marlonribunal.com/finding-gaps-in-or-missing-dates-using-tsql/

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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
red_75116

8/22/2022 - Mon
PortletPaul

If you are doing this on a regular basis then you should seriously consider having a permanent "calendar table"
see: https://www.experts-exchange.com/articles/12267/SQL-Server-Calendar-Table.html

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.
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
red_75116

ASKER
Thanks!  This was very helpful
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck