SQL - Finding non-overlap days when comparing two tables of date ranges

Question

I am using Microsoft SQL Server 2018. I have two tables with date ranges. One of employee IDs and dates of employment and another of employee IDs and date worked. Everyone day of employment needs to have a corresponding date worked but I need to find the days of employment where there is no date worked.

Table 1    
Row, EmployID, EmployStart, EmployEnd
1   1   1/1/2016    12/31/2016
2   1   1/1/2017    12/31/2017
3   1   1/1/2018    12/31/2018
4   2   1/1/2016    12/31/2016
5   2   1/1/2017    12/31/2017
6   2   1/1/2018    12/31/2018              
7   3   1/1/2016    12/31/2016
8   3   1/1/2017    12/31/2017
9   3   1/1/2018    12/31/2018              
10  4   1/1/2016    12/31/2016
11  4   1/1/2017    12/31/2017
12  4   1/1/2018    12/31/2018              
13  5   1/1/2016    12/31/2016
14  5   1/1/2017    12/31/2017
15  5   1/1/2018    12/31/2018

Table 2
Row EmployID    WorkStart   WorkEnd
1   1   1/1/2016    12/31/2016
2   1   1/1/2017    12/31/2017
3   1   1/1/2018    12/31/2018  
4   2   1/1/2016    12/31/2016
5   2   1/1/2017    12/31/2017                      
6   3   1/1/2016    12/31/2016
7   3   1/1/2017    12/31/2017
8   3   1/1/2018    6/30/2018       
9   4   1/1/2016    12/31/2016
10  4   5/1/2017    12/31/2017
11  4   1/1/2018    12/31/2018
12  5   1/1/2016    12/31/2016
13  5   1/1/2017    12/31/2017
14  5   1/1/2018    12/31/2018

So in this data set, I want to call out rows 6, 9, and 11 of table 1 because there are dates of employment without dates worked. And then have the results be only the dates of not worked.

So the results would look like

Row EmployID    MissStart   MissEnd
1   2      1/1/2018    12/31/2018
2   3      7/1/2018    12/31/2018
3   4      1/1/2017    4/30/2017

I currently have this

select *
from table1 a
left join table2 b
    on a.employid = b.employid
    and (
        cast(a.employstart as date)between cast(b.workstart as date) and cast(b.workend as date)
        or cast(a.employend as date)between cast(b.workstart as date) and cast(b.workend as date)
        )
where b.employid is null

why you save those date as text instead of dates? Save it as dates so you can use index. — Juan Carlos Oropeza, Nov 29 '17 at 14:20
You might take a peek at this article also. https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common — Sean Lange, Nov 29 '17 at 14:23

score 0 · Answer 1 · answered Nov 29 '17 at 14:21

Check for overlaps

Determine Whether Two Date Ranges Overlap

select *
from table1 a
left join table2 b
    on a.employid = b.employid
    and (   cast(a.employstart as date) <= cast(b.workend as date) 
        and cast(a.employend as date) >= cast(b.workstart as date) 
        )
where b.employid is null

SQL - Finding non-overlap days when comparing two tables of date ranges

1 Answers1