如何筛选Case ID在CLSD状态前（含该状态）的数据集记录

需求明确 #

• 针对包含多个Case ID的数据集，每个Case ID会经历若干不固定流程状态，最终需进入CLSD状态完成关闭
• 已进入CLSD状态的Case ID：仅保留CLSD状态行及在其之前的所有记录（需排除FromDateTime或EventNumber晚于/大于CLSD行的记录，比如示例中Case ID 789101要去掉CLSD之后的NCMP、PLIP记录）
• 未进入CLSD状态的Case ID（通过ToDateTimeWithNull列为NULL判断当前活跃）：保留所有记录

实现示例（以SQL为例） #

假设数据集表名为case_status，核心字段为CaseID、EventNumber、FromDateTime、Status、ToDateTimeWithNull，可通过窗口函数定位每个Case ID的CLSD状态基准值，再完成筛选：

WITH case_clsd_baseline AS (
    SELECT
        CaseID,
        -- 获取该Case的CLSD状态对应的最大EventNumber
        MAX(CASE WHEN Status = 'CLSD' THEN EventNumber END) AS clsd_max_event,
        -- 获取该Case的CLSD状态对应的最晚FromDateTime
        MAX(CASE WHEN Status = 'CLSD' THEN FromDateTime END) AS clsd_latest_datetime
    FROM case_status
    GROUP BY CaseID
)
SELECT cs.*
FROM case_status cs
JOIN case_clsd_baseline cb ON cs.CaseID = cb.CaseID
WHERE
    -- 未进入CLSD的活跃Case，全部保留
    (cb.clsd_max_event IS NULL AND cs.ToDateTimeWithNull IS NULL)
    -- 已进入CLSD的Case，仅保留CLSD及之前的记录
    OR (cb.clsd_max_event IS NOT NULL 
        AND cs.EventNumber <= cb.clsd_max_event 
        AND cs.FromDateTime <= cb.clsd_latest_datetime)

逻辑说明 #

1. 先通过CTEcase_clsd_baseline为每个Case ID计算出CLSD状态对应的最大EventNumber和最晚FromDateTime，无CLSD状态的则为NULL
2. 主查询通过关联基准表分两种情况筛选：
   • 无CLSD基准值且ToDateTimeWithNull为NULL的活跃Case，直接保留所有记录
   • 有CLSD基准值的Case，仅保留EventNumber和FromDateTime均不超过CLSD行对应值的记录，确保只保留CLSD及之前的状态

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