title: SQL - Finding non-overlap days when comparing two tables of date ranges
body: <p>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. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>
  
  <p>So the results would look like</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I currently have this </p>
  
  <pre><code>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
  </code></pre>