sql - 在 T-SQL 中找到正确的日期对并消除重叠的日期对

Question

我有一个日期，就像startdate一列和Enddate另一列一样。我需要在sql中找到消除数据中的连续日期范围。我需要找到重叠的项目，我需要删除。我已经使用一个代码来查找重叠的项目。我给出startdate和enddate作为参数。

我用来查找重叠的代码

Select * from #t 
where
((cast(@StartDate as datetime2)>=StartDate and cast(@EndDate as datetime2)<=EndDate)
                    OR (StartDate>= cast(@StartDate as datetime2) and EndDate<= cast(@EndDate as datetime2))
                    OR (cast(@StartDate as datetime2)>=StartDate AND cast(@StartDate as datetime2)<=EndDate)
                    OR (cast(@EndDate as datetime2)>=StartDate AND cast(@EndDate as datetime2)<=EndDate))

上面的查询可以找到正常的重叠，如

ID	开始日期	结束日期
1	2020 年 1 月 1 日	2020 年 1 月 11 日
2	2020 年 1 月 1 日	2021 年 1 月 3 日

在上述情况下，我将删除一个数据并保留其他数据

但它在以下类型的数据示例中失败。当运行以下类型的查询时，1 id 与 2 重叠，2 与 1 和 3 重叠。所以它显示 1 和 2 都删除。但在我的情况下不是删除 1 和 3。只需要删除 2。因为 2 在两个数据之间重叠，并且 1 和 3 已经处于良好的日期期间

例如

ID	开始日期	结束日期
1	2020 年 1 月 1 日	2020 年 1 月 11 日
2	2020 年 1 月 1 日	2021 年 1 月 3 日
3	2020 年 2 月 11 日	2022 年 5 月 4 日

在上面的例子中，我们有三对日期，id 1 和 3 在正确的间隔中，2 在两个 id 之间重叠。我需要找到重叠的一项或非重叠的项目。任何情况都可以让我找到结果。

我的预期结果是

ID	开始日期	结束日期
2	2020 年 1 月 1 日	2021 年 1 月 3 日

另一个例子是

ID	开始日期	结束日期
1	2020 年 1 月 1 日	2020 年 1 月 11 日
2	2020 年 2 月 11 日	2022 年 6 月 5 日
3	2020 年 2 月 11 日	2022 年 5 月 4 日

上面如果您看到 1 和 2 在正确的日期期间，但 id 3 与 2 id 重叠。现在我只想找到重叠的结果，我不需要其他数据。

另一个例子是

ID	开始日期	结束日期
3	2020 年 2 月 11 日	2022 年 5 月 4 日

标签： sqlsql-servertsqloverlap

Answer 1

我使用了第二组数据，但这也适用于第一组数据。但我对你的第一个记录集预期输出有疑问。如果你能清除它，我可以再检查一次，

  Create table OverlapData_1
  (
   id int
  , Startdate date
  , EndDate date
  )

 insert into  OverlapData_1 values(1, '01/01/2020','01/11/2020')
 insert into  OverlapData_1 values(2, '01/01/2020','01/03/2021')
 insert into  OverlapData_1 values(3, '02/11/2020','05/04/2022')

    SELECT  A.[id]
      ,A.Startdate
     ,A.EndDate FROM
      (
     SELECT
        CASE WHEN Startdate between LAG(StartDate) OVER ( order by id) and LAG(EndDate) OVER ( order by id)    THEN 1 else 0 end as  [status_1]
       , CASE WHEN EndDate between LAG(StartDate) OVER ( order by id)  and  LAG(EndDate) OVER ( order by id)   THEN 1 else 0 end as  [status_2]
      , CASE WHEN StartDate between LEAD(StartDate) OVER ( order by id) and  LEAD(EndDate) OVER ( order by id)  THEN 1 else 0 end as  [status_3]
      , CASE WHEN EndDate between LEAD(StartDate) OVER ( order by id)  and  LEAD(EndDate) OVER ( order by id)   THEN 1 else 0 end as  [status_4]
  ,*
   FROM OverlapData_1
 ) AS A 
    WHERE (A.status_1 = 1 AND A.status_2 = 1)       
      OR (A.status_1 = 1 AND A.status_4 = 1)


   
   Create table OverlapData_2
   (
     id int
    , Startdate date
   , EndDate date
    )

   insert into  OverlapData_2 values(1, '01/01/2020','01/11/2020')
   insert into  OverlapData_2 values(2, '01/01/2020','06/05/2022')
   insert into  OverlapData_2 values(3, '02/11/2020','05/04/2022')


SELECT  A.[id]
  ,A.Startdate
 ,A.EndDate FROM
(
    SELECT
       CASE WHEN Startdate between LAG(StartDate) OVER ( order by id) and LAG(EndDate) OVER ( order by id)    THEN 1 else 0 end as  [status_1]
     , CASE WHEN EndDate between LAG(StartDate) OVER ( order by id)  and  LAG(EndDate) OVER ( order by id)   THEN 1 else 0 end as  [status_2]
    , CASE WHEN StartDate between LEAD(StartDate) OVER ( order by id) and  LEAD(EndDate) OVER ( order by id)  THEN 1 else 0 end as  [status_3]
   , CASE WHEN EndDate between LEAD(StartDate) OVER ( order by id)  and  LEAD(EndDate) OVER ( order by id)   THEN 1 else 0 end as  [status_4]
      ,*
   FROM OverlapData_2
  ) AS A 
   WHERE (A.status_1 = 1 AND A.status_2 = 1)       
     OR (A.status_1 = 1 AND A.status_4 = 1)