如何将一组SQL记录转换为基于最大值和最小值的单行?

Question

我们将员工状态历史记录存储在SQL Server表中。有员工ID(外键)，Start &"结束日期"one_answers"员工状态"列。结束日期列中的NULL表示该状态现在处于活动状态。

员工可以处于活动状态，也可以被终止，然后重新激活。

由于客户端应用程序中的错误，每次更新员工配置文件时，它都会在表中生成新记录，其中开始日期是前一条记录的结束日期(参见记录# 1,2,3)。例如，1号员工从1月1日到4月1日一直在工作，然后他们被解雇了三个月，然后又被重新雇用了。

tbody> <<tr>245678

记录ID	员工ID	起始日期	结束日期	员工状态
1	1	2019-01-01	2019-02-01	活动
	1	2019-02-01	2019-03-01	活动
3	1	2019-03-01	2019-04-01	活动
	1	2019-04-01	2019-07-01	终止
	1	2019-07-01	空	活动
	2	2019-01-01	2019-02-01	活动
	2	2019-01-01	空	活动
	3	2019-01-01	空	活动

这是一个缺口和孤岛问题的例子。我建议使用lag()和累积的总和来识别组:

select min(recordid) as recordid, employeeid, status, min(startdate), max(enddate)
from (select t.*,
sum(case when prev_enddate = startdate then 0 else 1 end) over (partition by employeeid order by startdate) as grp
from (select t.*,
lag(enddate) over (partition by employeeid, status order by startdate) as prev_enddate
from t
) t
) t
group by employeeid, status, grp;

基本上，这将查看相同员工和状态的前一行。如果不与当前行相邻，则当前行开始一个新组。