亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

本文介紹了合并日期：前一條記錄后的日期間隔為零或一天的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！

問題描述

在SQL Server中，當(dāng)開始日期在結(jié)束日期之后或開始日期等于結(jié)束日期時，我要根據(jù)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的結(jié)果，它不應(yīng)該合并與ID 100相關(guān)的所有記錄

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 ;

這篇關(guān)于合并日期：前一條記錄后的日期間隔為零或一天的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，