red_75116
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! This was very helpful
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.