基于日期合并状态变更记录：按ClientID分组转换多行状态数据

需求概述 #

将Accountx表中同一ClientID的多行状态变更记录，基于StartDate排序后合并为状态流转记录，每行展示上一状态（FromStatus）、下一状态（ToStatus）、当前状态的生效开始时间，以及下一状态的生效时间作为当前状态的结束时间；最后一条状态记录的ToStatus和EndDate为NULL。

表结构与测试数据 #

CREATE TABLE Accountx
( ID INT,
CompanyID INT,
[Status] nvarchar(10),
ClientID INT,
StartDate DateTime
) ON [PRIMARY]

INSERT INTO Accountx VALUES
(1,1,'New', 44,'2020-06-18 11:54:00'),
(2,1,'Active',44,'2020-06-18 11:57:00'),
(3,1,'Inactive',44,'2020-06-18 12:09:00')

当前数据输出 #

ID  CompanyID   Status  ClientID    StartDate
1    1          New       44        2020-06-18 11:54:00.000
2    1          Active    44        2020-06-18 11:57:00.000
3    1          Inactive  44        2020-06-18 12:09:00.000

期望输出（修正笔误：第三行StartDate应为2020-06-18 12:09:00） #

CompanyID  FromStatus ToStatus ClientID StartDate            EndDate
    1        New        Active     44     2020-06-18 11:54:00  2020-06-18 11:57:00
    1        Active     Inactive   44     2020-06-18 11:57:00  2020-06-18 12:09:00
    1        Inactive   NULL       44     2020-06-18 12:09:00  NULL

解决方案SQL #

SELECT 
    CompanyID,
    [Status] AS FromStatus,
    LEAD([Status]) OVER (PARTITION BY ClientID ORDER BY StartDate) AS ToStatus,
    ClientID,
    StartDate,
    LEAD(StartDate) OVER (PARTITION BY ClientID ORDER BY StartDate) AS EndDate
FROM Accountx
ORDER BY StartDate

逻辑说明 #

• 使用LEAD()窗口函数，按ClientID分组、StartDate排序，获取当前行的下一行的Status和StartDate，分别作为ToStatus和EndDate
• 最后一行无后续数据，LEAD()返回NULL，符合期望输出要求
• 最终按StartDate排序，保证状态流转顺序正确

内容的提问来源于stack exchange，提问作者Miles Colby