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;

如果运行查询，则会错误地计算2012年7月1日至2012年11月1日之间的就业空白期，这是不正确的，因为该记录的开始日期和结束日期在前一个记录之间开始。对于这些空白期，查询应生成以下结果集：

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

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

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

我的问题是，我正在尝试找到一种方法来正确计算它，而不需要使用游标，因为我一直在尝试的方式涉及循环遍历EmploymentGapInfo表变量中的每个记录，一旦我拥有该记录的开始和结束日期，就再次循环遍历该表，以查看开始日期和结束日期是否与表中的其他记录重叠。

有没有办法用基于集合的方法解决这个问题，而不是尝试使用游标？这是否是在业务层完成而不是尝试在数据库中解决它的问题？

任何帮助将不胜感激。

- rc6886

---

尝试创建一个SqlFiddle演示，并在此处提供链接。 - th1rdey3

最后一个间隔（03/05/2013 -> 10/14/2013）是错误的，因为有这一行 '10/14/2012 -> NULL。 - Bogdan Sahlean

@th1rdey3：这是 SQL Fiddle 的链接：http://sqlfiddle.com/#!3/06753/3/0 - rc6886

@BogdanSahlean：抱歉，应该是2013年10月14日，错误已经更正。 - rc6886

Answer 1

这是一个针对SQL Server 2012及以上版本的优秀解决方案。所有功劳归于ask tom上的A Boucher （链接）

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

- gmken

---

我知道这已经很老了，但我只是想说谢谢！这完美地解决了我遇到的一个非常相似的问题，并为我省去了许多麻烦。 - APH

Answer 2

您可以创建一个临时表，其中包含按日期升序排列的@EmploymentGapInfo中的所有日期。然后更容易找出这些间隔。

获取所有日期的查询可能如下所示：

select distinct StartDate 'GDate'
from @EmploymentGapInfo
union
select distinct EndDate 
from @EmploymentGapInfo

- Jonysuise

Answer 3

也许这是一篇有些老旧的帖子，但这里有答案：

首先，你应该排除存在重叠范围的可能性，并将结果表示为非重叠范围列表。这并不是一件容易的事情，你可以用以下查询来完成：

    SELECT *
    INTO #TEMP
    FROM
    (
          SELECT DriverQualificationApplicationEmploymentId, DriverQualificationApplicationId, Min(NewStartDate) StartDate, MAX(EndDate) EndDate
          FROM
          (
                SELECT DriverQualificationApplicationEmploymentId, DriverQualificationApplicationId, StartDate, EndDate,
                      NewStartDate = Range_UNTIL_NULL.StartDate + NUMBERS.number,
                      NewStartDateGroup =     DATEADD(d, 
                                                  1 - DENSE_RANK() OVER (PARTITION BY DriverQualificationApplicationEmploymentId, DriverQualificationApplicationId ORDER BY Range_UNTIL_NULL.StartDate + NUMBERS.number), 
                                                  Range_UNTIL_NULL.StartDate + NUMBERS.number)
                FROM 
                (
                      SELECT 
                            DriverQualificationApplicationEmploymentId, DriverQualificationApplicationId, StartDate, ISNULL(EndDate, dateadd(d,1,StartDate)) AS EndDate
                      FROM @EmploymentGapInfo T1
                      WHERE
                            NOT  EXISTS (   SELECT * 
                                            FROM @EmploymentGapInfo t2 
                                            WHERE  T1.DriverQualificationApplicationEmploymentId = t2.DriverQualificationApplicationEmploymentId AND
                                                   T1.DriverQualificationApplicationId = T2.DriverQualificationApplicationId and 
                                                   T1.StartDate > T2.StartDate AND T2.EndDate IS NULL
                                        )
                )  AS Range_UNTIL_NULL
                CROSS APPLY  Enumerate ( ABS(DATEDIFF(d, StartDate, EndDate))) AS NUMBERS
                      ) X
          GROUP BY DriverQualificationApplicationEmploymentId, DriverQualificationApplicationId, NewStartDateGroup
    ) OVERLAPED_RANGES_WITH_COUNT
    ORDER BY DriverQualificationApplicationEmploymentId, DriverQualificationApplicationId, StartDate    

然后您可以计算超过30天的间隔：

SELECT 
    EndDate AS StartGap, 
    ( SELECT MIN(StartDate) FROM #temp t3 WHERE t3.startdate  > t1.endDate) AS EndGap 
FROM #TEMP t1
WHERE EndDate + 30 < (SELECT Min(startDate) FROM #temp t2 WHERE t2.startdate  > t1.endDate) 

- Qsebas