Link to home
Start Free TrialLog in
Avatar of red_75116
red_75116Flag 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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of red_75116

ASKER

Thanks!  This was very helpful