合并日期：前一条记录后的日期间隔为零或一天

在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;

---

解决方案

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 ;