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

Question

我有一个ID为，状态和员工日期范围的数据集。
下面给出的输入数据集是一名员工的详细信息。
记录中的日期范围是连续的（按确切顺序），因此第二行的开始日期将是第一行的结束日期的下一个日期。

如果员工连续休假不同的月份，则该表将存储日期范围不同的信息。
例如：在输入集中，员工已从‘16 -10-2016’休病假到‘31 -12-2016’，并在‘1-1-2017’重新加入。
因此，该项目有3条记录，但日期是连续的。在输出中，我需要将此作为一条记录，如预期的输出数据集中所示。

输入

Id  Status   StartDate   EndDate

1   Active   1-9-2007    15-10-2016
1   Sick     16-10-2016  31-10-2016
1   Sick     1-11-2016   30-11-2016
1   Sick     1-12-2016   31-12-2016
1   Active   1-1-2017    4-2-2017  
1   Unpaid   5-2-2017    9-2-2017  
1   Active   10-2-2017   11-2-2017 
1   Unpaid   12-2-2017   28-2-2017 
1   Unpaid   1-3-2017    31-3-2017 
1   Unpaid   1-4-2017    30-4-2017 
1   Active   1-5-2017    13-10-2017
1   Sick     14-10-2017  11-11-2017
1   Active   12-11-2017  NULL

预期的输出

Id   Status    StartDate    EndDate

1    Active    1-9-2007     15-10-2016
1    Sick      16-10-2016   31-12-2016
1    Active    1-1-2017     4-2-2017  
1    Unpaid    5-2-2017     9-2-2017  
1    Active    10-2-2017    11-2-2017 
1    Unpaid    12-2-2017    30-4-2017 
1    Active    1-5-2017     13-10-2017
1    Sick      14-10-2017   11-11-2017
1    Active    12-11-2017   NULL

我不能按id，status进行min（startdate）和max（EndDate）组，因为如果同一位员工又请了一次病假，那么该结束日期（示例中为“11-11-2017”）将作为结束日期日期。

谁能帮我解决SQL Server 2014中的查询问题？

Answer 1

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

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

;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, grp

GO

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在这里