SQL Server 2014 如何从多行数据生成员工部门变动日期范围

需要从员工操作审计数据中生成连续的部门任职日期范围，提取员工每次切换部门对应的任职起止时间。原有查询在员工从某部门调出后再次调回原部门时，无法返回正确结果，当前数据库环境为SQL Server 2014。

原有实现与错误结果 #

测试表结构与模拟数据：

declare @Audits table 
(
  EmployeeId int,
  DepartmentCode varchar(10),
  ActionTaken varchar(50),
  ActionDate date
)

insert into @Audits values (1, '978', 'Update Name', '2022-1-1')
insert into @Audits values (1, '978', 'Update Salary', '2022-2-1')
insert into @Audits values (1, '928', 'Update Department', '2022-3-1')
insert into @Audits values (1, '978', 'Update Role', '2022-4-1')
insert into @Audits values (1, '978', 'Update Job', '2022-5-1')
insert into @Audits values (1, '911', 'Update Department', '2022-6-1')
insert into @Audits values (1, '911', 'Update Salary', '2022-7-1')
insert into @Audits values (1, '911', 'Update Job', '2022-8-1')

原有查询逻辑是直接按员工、部门分组取最小操作日期，再用LEAD()函数计算结束日期：

select
  EmployeeId,
  DepartmentCode,
  ActionDate as StartDate,
  EndDate = isnull(lead(ActionDate, 1) over (partition by EmployeeId order by ActionDate), '9999-12-31')
from
(
  select EmployeeId, DepartmentCode, min(ActionDate) as ActionDate
  from @Audits
  group by EmployeeId, DepartmentCode
) d
order by EmployeeId, ActionDate;

该查询返回错误结果，合并了员工两次在978部门的不连续任期，也错误计算了928部门的结束日期：

EmployeeId  DepartmentCode StartDate  EndDate
----------- -------------- ---------- ----------
1           978            2022-01-01 2022-03-01
1           928            2022-03-01 2022-06-01
1           911            2022-06-01 9999-12-31

期望得到的正确结果如下，需要识别出员工2022年4月调回978部门的单独任职段：

EmployeeId  DepartmentCode StartDate  EndDate
----------- -------------- ---------- ----------
1           978            2022-01-01 2022-03-01
1           928            2022-03-01 2022-04-01
1           978            2022-04-01 2022-06-01
1           911            2022-06-01 9999-12-31

这是典型的间断孤岛问题（Gaps and Islands），不能直接按部门编码分组，需要先识别相邻的连续同部门记录段，再计算每个段的起止日期，SQL Server 2014已支持所需的窗口函数，实现步骤如下：

1. 按员工分区、操作日期排序，用LAG()函数获取上一条记录的部门编码，判断当前行部门与上一行是否发生变化，发生变化则标记为分段点
2. 对分段标记做累计求和，为每一段连续的同部门任职记录生成唯一的分组ID
3. 按员工、部门编码、分组ID聚合，取每个分组的最小操作日期作为段起始日期，再用LEAD()函数获取下一段的起始日期作为当前段的结束日期，最后一段默认结束日期为9999-12-31

完整可运行代码：

;with Step1 as (
    -- 标记部门切换的分段点
    select 
        EmployeeId,
        DepartmentCode,
        ActionDate,
        case when DepartmentCode = lag(DepartmentCode,1) over(partition by EmployeeId order by ActionDate) 
             then 0 
             else 1 
        end as IsNewGroup
    from @Audits
),
Step2 as (
    -- 生成分组ID，相同连续段的GroupId一致
    select
        EmployeeId,
        DepartmentCode,
        ActionDate,
        sum(IsNewGroup) over(partition by EmployeeId order by ActionDate rows between unbounded preceding and current row) as GroupId
    from Step1
)
-- 按分组聚合计算起止日期
select
    EmployeeId,
    DepartmentCode,
    min(ActionDate) as StartDate,
    isnull(lead(min(ActionDate),1) over(partition by EmployeeId order by min(ActionDate)), '9999-12-31') as EndDate
from Step2
group by EmployeeId, DepartmentCode, GroupId
order by EmployeeId, StartDate

运行上述代码即可得到期望的正确结果，该逻辑兼容员工多次在同一部门间来回调动的场景，不会合并不连续的同部门任期。

内容的提问来源于stack exchange，提问作者John D