SQL Server 2012：筛选无cancelled的Case_Number及计算状态时间差

1. 获取未出现'cancelled'状态的Case_Number #

以下两种方法均可实现需求，根据数据量选择即可：

方法1：使用NOT EXISTS（推荐大数据量场景） #

SELECT DISTINCT Case_Number
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.Case_Number = t1.Case_Number
      AND t2.Status = 'cancelled'
);

方法2：使用GROUP BY + HAVING #

SELECT Case_Number
FROM your_table
GROUP BY Case_Number
HAVING SUM(CASE WHEN Status = 'cancelled' THEN 1 ELSE 0 END) = 0;

两种执行结果均为C001、C002、C004。

2. 获取下一个状态、时间戳并计算时间差 #

利用SQL Server 2012支持的LEAD()窗口函数，按案例分组、时间排序后直接提取下一条状态数据：

SELECT 
    Case_Number,
    Status AS CurrentStatus,
    Time AS CurrentTime,
    LEAD(Status) OVER (PARTITION BY Case_Number ORDER BY Time) AS NextStatus,
    LEAD(Time) OVER (PARTITION BY Case_Number ORDER BY Time) AS NextStatusTime,
    DATEDIFF(SECOND, Time, LEAD(Time) OVER (PARTITION BY Case_Number ORDER BY Time)) AS StatusDurationSeconds
FROM your_table;

• PARTITION BY Case_Number：按案例编号分组处理
• ORDER BY Time：确保按时间顺序提取后续状态
• 最后一条状态的NextStatus和StatusDurationSeconds会返回NULL，符合无后续状态的逻辑

结合两个需求：仅保留无取消状态的案例数据 #

WITH NoCancelledCases AS (
    SELECT DISTINCT Case_Number
    FROM your_table t1
    WHERE NOT EXISTS (
        SELECT 1
        FROM your_table t2
        WHERE t2.Case_Number = t1.Case_Number
          AND t2.Status = 'cancelled'
    )
)
SELECT 
    t.Case_Number,
    t.Status AS CurrentStatus,
    t.Time AS CurrentTime,
    LEAD(t.Status) OVER (PARTITION BY t.Case_Number ORDER BY t.Time) AS NextStatus,
    LEAD(t.Time) OVER (PARTITION BY t.Case_Number ORDER BY t.Time) AS NextStatusTime,
    DATEDIFF(SECOND, t.Time, LEAD(t.Time) OVER (PARTITION BY t.Case_Number ORDER BY t.Time)) AS StatusDurationSeconds
FROM your_table t
JOIN NoCancelledCases nc ON t.Case_Number = nc.Case_Number;

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