SQL Server - 查找包含重叠日期的日期之间的间隔

Question

我最近的任务是查找工作日期之间的差距，其中差距的定义是从一项工作结束到下一项工作开始的时间跨度超过 30 天，并且能够提出适合该情况的查询，请参见下文:

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;

我正在将一个记录与下一个记录进行比较，看看第一个记录的结束日期和第二个记录的开始日期之间是否有 30 天的间隔，这对于“正常”来说效果很好“情况，即工作期限不重叠的情况。嗯，这种特殊情况已经出现，某人在特定时间段内拥有多个工作，并且工作 A 的时间段位于该员工从事工作 B 的时间段之间。以下是使用上述查询运行的测试数据:

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;

如果您运行查询，则会错误地计算 07/01/2012 和 11/01/2012 之间的就业差距，这是不正确的，因为该记录的开始日期和结束日期在上一条记录之间开始。查询应针对间隙生成以下结果集:

2003年7月11日 -> 2006年8月28日

2011年6月15日 -> 2011年8月22日

2013年5月3日 -> 2013年10月14日

我的问题是，我正在尝试想出一种无需光标即可正确计算的方法，因为我一直在尝试的方法将涉及循环遍历EmploymentGapInfo表变量中的每条记录，并且一旦我该记录的开始日期和结束日期，再次循环该表以查看开始日期和结束日期是否与表中的任何其他记录重叠。

有什么方法可以使用基于集合的方法来解决这个问题，而不是尝试使用游标？在业务层中解决这个问题是否会比在数据库中解决它更有好处？

如有任何帮助，我们将不胜感激。

Answer 1

适用于 sql server 2012 及更高版本的出色解决方案。所有功劳都归功于 A Boucher 的询问汤姆 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