来自具有连续日期范围记录的数据集中的最小日期和最大日期的日期范围

来自具有连续日期范围记录的数据集中的最小日期和最大日期的日期范围

这是“分组和窗口”的一个示例。

• 首先，您为每个状态设置一个重置点
• 设置组的总和
• 然后获取每个组的最大/最小日期。

;with x as(    select Id, Status, StartDate,  EndDate,iif (lag(Status) over (order by Id, StartDate) = Status,

null, 1) rst
from emp
), y as
(
select Id, Status, StartDate, EndDate,
sum(rst) over (order by Id, StartDate) grp
from x
)

 select Id,        MIN(Status) as Status,        MIN(StartDate) StartDate,        MAX(EndDate) EndDate from   y group by Id, grp order by Id, grpGO

Id | Status | StartDate| EndDate -: | :----- | :------------------ | :------------------ 1 | Active | 01/09/2007 00:00:00 | 15/10/2016 00:00:00 1 | Sick   | 16/10/2016 00:00:00 | 31/12/2016 00:00:00 1 | Active | 01/01/2017 00:00:00 | 04/02/2017 00:00:00 1 | Unpaid | 05/02/2017 00:00:00 | 09/02/2017 00:00:00 1 | Active | 10/02/2017 00:00:00 | 11/02/2017 00:00:00 1 | Unpaid | 12/02/2017 00:00:00 | 30/04/2017 00:00:00 1 | Active | 01/05/2017 00:00:00 | 13/10/2017 00:00:00 1 | Sick   | 14/10/2017 00:00:00 | 11/11/2017 00:00:00 1 | Active | 12/11/2017 00:00:00 | null        

dbfiddle在这里