通过在SQL Server中放置滞后来输出数据

Question

我在SQL Server中有一个表，如下所示。

SELECT * FROM OverlappingDateRanges

Id          startDate                       EndDate
10001   2020-04-01 00:00:00.000     2020-05-25 00:00:00.000
10001   2020-05-26 00:00:00.000     2020-07-15 00:00:00.000
10001   2020-07-17 00:00:00.000     2020-08-15 00:00:00.000
10001   2020-08-16 00:00:00.000     2020-10-15 00:00:00.000
10001   2020-10-16 00:00:00.000     2020-12-31 00:00:00.000
10002   2020-05-01 00:00:00.000     2020-05-29 00:00:00.000
10002   2020-05-30 00:00:00.000     2020-07-08 00:00:00.000
10002   2020-07-09 00:00:00.000     2020-10-01 00:00:00.000
10002   2020-10-03 00:00:00.000     2020-12-31 00:00:00.000

---

我希望输出如下，如果相同id的结束日期和下一个开始日期之间没有日期差，那么日期将继续&如果结束日期和下一个开始日期不在continue中，则它应该断开。

输出应为：

id          startDate                endDate
10001  2020-04-01 00:00:00.000    2020-07-15 00:00:00.000
10001  2020-07-17 00:00:00.000    2020-12-31 00:00:00.000
10002  2020-05-01 00:00:00.000    2020-10-01 00:00:00.000
10002  2020-10-03 00:00:00.000    2020-12-31 00:00:00.000

Answer 1

这是一种间隙和孤岛问题。通过查看前一行的末尾来确定每个输出行的起始位置。然后进行累计求和和：

select id, min(startdate), max(enddate)
from (select t.*,
sum(case when prev_enddate >= dateadd(day, -1, startdate) then 0 else 1
end) over (partition by id order by startdate) as grp
from (select t.*,
lag(enddate) over (partition by id order by startdate) as prev_enddate
from t
) t
) t
group by id, grp;

这里有一个db<gt；不停摆弄