SQL Server快照模型Gaps and Islands问题：场景2查询修正求助

场景1：状态为Pending Validation/Closed时的正确实现 #

当源数据中issue_status_name为Pending Validation或Closed时，以下SQL可正确计算状态延续的起始日期：

WITH cte AS (
    SELECT
        as_of_date,
        issue_id,
        issue_status_name,
        ROW_NUMBER() OVER (PARTITION BY issue_id ORDER BY as_of_date) 
        - ROW_NUMBER() OVER (PARTITION BY issue_id, output_flag ORDER BY as_of_date) AS grp,
        output_flag
    FROM issues 
    CROSS APPLY (
        VALUES (CASE WHEN issue_status_name IN ('Pending Validation', 'Closed') THEN 1 ELSE 0 END)
    ) v(output_flag)
)
SELECT
    as_of_date,
    issue_id,
    issue_status_name,
    CASE WHEN output_flag = 1 THEN MIN(as_of_date) OVER (PARTITION BY issue_id, grp) END AS desired_output
FROM cte
ORDER BY as_of_date

场景2：基于前置状态的延续需求修正 #

需求说明 #

仅当**前置状态prev_status_name为Pending Sustainability，且当前状态issue_status_name为Pending Validation或Closed**时，获取该记录的as_of_date并向后延续；其余情况设为NULL。

原代码的问题 #

原SQL仅将满足条件的单条记录标记为output_flag=1，后续需要延续的行未被纳入同一分组，导致分组逻辑失效，无法实现日期向后延续的效果。

修正后的SQL #

WITH cte AS (
    SELECT
        as_of_date,
        issue_id,
        issue_status_name,
        prev_status_name,
        -- 标记触发延续的起始行
        CASE WHEN issue_status_name IN ('Pending Validation', 'Closed') 
             AND prev_status_name = 'Pending Sustainability' THEN 1 ELSE 0 END AS trigger_flag,
        -- 累计触发标记，将起始行及后续行归为同一分组
        SUM(CASE WHEN issue_status_name IN ('Pending Validation', 'Closed') 
                  AND prev_status_name = 'Pending Sustainability' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY issue_id ORDER BY as_of_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
    FROM issues
),
grp_start AS (
    -- 获取每个分组的起始日期（即触发行的as_of_date）
    SELECT
        issue_id,
        grp,
        MIN(CASE WHEN trigger_flag = 1 THEN as_of_date END) AS start_date
    FROM cte
    GROUP BY issue_id, grp
)
SELECT
    c.as_of_date,
    c.issue_id,
    c.issue_status_name,
    -- 仅延续区间显示起始日期，其余为NULL
    gs.start_date AS desired_output
FROM cte c
LEFT JOIN grp_start gs ON c.issue_id = gs.issue_id AND c.grp = gs.grp
ORDER BY c.issue_id, c.as_of_date;

修正逻辑说明 #

1. 标记触发行：用trigger_flag标记满足前置状态+当前状态条件的起始记录；
2. 生成延续分组：通过SUM() OVER()窗口函数累计触发标记，将起始行及后续所有连续行归为同一grp分组；
3. 提取分组起始日期：单独计算每个分组的起始日期（即触发行的as_of_date）；
4. 关联输出：将原表与分组起始日期关联，实现延续区间显示起始日期、非延续区间显示NULL的效果。

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