如何用T-SQL获取ConfirmationStarted前最近的Closed状态变更日期？

问题分析 #

原查询通过LEAD()获取下一个状态，仅能匹配Closed直接转为ConfirmationStarted的场景，但当中间存在其他状态（如示例中的Awarded）时，该条件无法命中目标记录，导致无法得到正确结果。

修改后的查询（方法一：关联查询） #

WITH ConfirmationEvents AS (
    -- 先获取所有进入ConfirmationStarted状态的时间点
    SELECT 
        ID,
        CreatedAt AS CS_CreatedAt
    FROM tableA
    WHERE NewStatus = 'ConfirmationStarted'
)
SELECT 
    ce.ID,
    MAX(a.CreatedAt) AS xdate
FROM ConfirmationEvents ce
INNER JOIN tableA a 
    ON a.ID = ce.ID 
    AND a.CreatedAt < ce.CS_CreatedAt  -- 仅取CS状态之前的记录
    AND a.NewStatus = 'Closed'         -- 筛选状态为Closed的记录
GROUP BY ce.ID;

修改后的查询（方法二：窗口函数） #

如果不想使用JOIN，可以用窗口函数直接标记每条记录之后的第一个ConfirmationStarted时间：

WITH StatusWithCSContext AS (
    SELECT 
        ID,
        CreatedAt,
        NewStatus,
        -- 找到当前记录之后（时间更晚）的第一个ConfirmationStarted状态的时间
        MIN(CASE WHEN NewStatus = 'ConfirmationStarted' THEN CreatedAt END)
            OVER (PARTITION BY ID ORDER BY CreatedAt DESC 
                  ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS Next_CS_Time
    FROM tableA
)
SELECT 
    ID,
    MAX(CreatedAt) AS xdate
FROM StatusWithCSContext
WHERE 
    NewStatus = 'Closed' 
    AND Next_CS_Time IS NOT NULL  -- 确保该Closed记录之后存在ConfirmationStarted状态
GROUP BY ID;

逻辑说明 #

两种方法的核心逻辑一致：

1. 定位所有进入ConfirmationStarted状态的时间点；
2. 在每个ID的历史记录中，找到这些时间点之前的所有Closed状态记录；
3. 取其中最新的一条记录的CreatedAt作为结果。

这样无论中间经过多少个其他状态，都能正确找到目标日期。

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