SQL Server - Find Gaps Between Dates that Include Overlapping Dates

Question

I was recently tasked with finding gaps between dates of employments, where a gap is defined as having a time span of greater than 30 days from the end of one job to the start of the next, and was able to come up with a query to suit that, see below:

WITH GapsInEmployment AS
(
    SELECT 
    -1 AS DriverQualificationApplicationEmploymentGapId
    ,E1.DriverQualificationApplicationId
    ,E1.EndDate AS EmploymentGapBeginDate
    ,E2.StartDate AS EmploymentGapEndDate
    ,(
        CASE 
            WHEN ISNULL(DATEDIFF(DD, E1.EndDate, E2.StartDate), 0) < 0 THEN 0
            ELSE DATEDIFF(DD, E1.EndDate, E2.StartDate)
        END
        ) AS DaysLapsedBetweenEmployment
        ,NULL AS ReasonForEmploymentGap
    FROM @EmploymentGapInfo E1
    LEFT JOIN @EmploymentGapInfo E2 
        ON E1.RowNum = E2.RowNum - 1
)

SELECT *
FROM GapsInEmployment
WHERE DaysLapsedBetweenEmployment > 30;

I'm doing a comparison of one record, to the next record, to see if there's an lapse of 30 days between the end date of the first record and the start date of the second one, which works fine for "normal" cases, i.e. where periods of employment don't overlap. Well that particular case has appeared where someone has more than one job during a particular time period, and the time period of Job A is between the period of time that the employee had Job B. Here's the test data to run with the query above:

DECLARE @EmploymentGapInfo TABLE
(
    RowNum INT IDENTITY(1, 1)
    ,DriverQualificationApplicationEmploymentId INT
    ,DriverQualificationApplicationId INT
    ,StartDate DATETIME
    ,EndDate DATETIME
);
INSERT INTO @EmploymentGapInfo
SELECT -1, 766, '10/14/2003', '11/07/2003';

INSERT INTO @EmploymentGapInfo
SELECT -1, 766, '08/28/2006', '06/15/2011';

INSERT INTO @EmploymentGapInfo
SELECT -1, 766, '08/22/2011', '10/23/2012';

INSERT INTO @EmploymentGapInfo
SELECT -1, 766, '06/01/2012', '07/01/2012';

INSERT INTO @EmploymentGapInfo
SELECT -1, 766, '11/01/2012', '03/05/2013';

INSERT INTO @EmploymentGapInfo
SELECT -1, 766, '10/14/2013', NULL;

If you run the query, it's incorrectly calculating an employment gap between 07/01/2012 and 11/01/2012, which is incorrect as the start date and end date for that record starts between the previous record. The query should produce the following result set for the gaps:

11/07/2003 -> 08/28/2006

06/15/2011 -> 08/22/2011

03/05/2013 -> 10/14/2013

My problem is, I'm trying to come up with a way to calculate this correctly without a cursor, as the way I've been trying would involve me looping through each record in the EmploymentGapInfo table variable, and once I have the start and end dates for that record, loop through the table again to see if the start date and end date overlap any other records in the table.

Is there any way I can tackle this problem with a set-based approach instead of trying to use a cursor? Would this be a problem that would benefit from having it done in the business layer versus trying to solve it in the database?

Any help would be appreciated.

Answer 1

Great solution for sql server 2012 and above. All the credit to A Boucher on ask tom https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:529176000346581356

select *
from (
      select 
       max(enddate) over (order by startdate) start_range
       ,lead(startdate) over (order by startdate) end_range
      from @EmploymentGapInfo
    ) as c
where c.start_range < c.end_range