sql - 合并前一个记录之后的日期有零天或一天间隔的日期

Question

当开始日期跟随结束日期或开始日期等于 SQL Server 中基于 ID 的结束日期时，我想将多个记录合并为单个记录，同时获取该组中的 MAX(ID2)

下面是示例输入和输出。还添加了输入表的 SQL 代码:

create table #T (ID1 INT, ID2 INT, StartDate DATE, EndDate DATE)

insert into #T values
(100, 764286, '2019-05-01', '2019-05-31'),
(100, 764287, '2019-06-01', '2019-06-30'),
(100, 764288, '2019-07-10', '2019-07-31'),
(101, 764289, '2020-02-01', '2020-02-29'),
(101, 764290, '2020-02-29', '2020-03-31'),
(102, 764291, '2021-10-01', '2021-10-31'),
(102, 764292, '2021-11-01', '2021-11-30'),
(102, 764293, '2021-11-30', '2021-12-31'),
(103, 764294, '2022-01-01', '2022-01-31');

这是我尝试过的脚本，但它没有给出我期望的 ID 100 的结果，它不应该合并与 ID 100 相关的所有记录

select m.ID1,
       NewID2  AS ID2,
       m.StartDate,
       lead(dateadd(day, -1, StartDate), 1, MaxEndDate) over (partition by ID1 order by StartDate) as EndDate
from (select *,
             lag(StartDate) over (partition by ID1 order by StartDate) as S1,
             lag(StartDate) over (partition by ID1 order by StartDate) as S2,
             max(EndDate) over (partition by ID1) as MaxEndDate,
             max(ID2) over (partition by ID1) as NewID2
      from #T
     ) m
where S2 is null or S1 <> S2;

Answer 1

fiddle

select id1, max(id2) as id2, min(startdate) as startdate, max(enddate) as enddate
from
(
  select *, sum(addone) over(partition by id1 order by startdate,enddate,id2 rows unbounded preceding) as grp
  from
  ( 
    select *, 
      case when startdate <= dateadd(day, 1, max(enddate) over(partition by id1 order by startdate,enddate,id2 rows between unbounded preceding and 1 preceding))
           then 0 
           else 1 
      end as addone
    from #T
   ) as r
) as g
group by id1, grp
order by id1, startdate ;