如何过滤合并同表记录，用TSQL视图计算工单在State B的停留时长

现有如下工单状态变更记录：

需要计算每个工单在State B的总停留时长，规则如下：

• 当工单有转入State B的记录后，若后续有转出State B的记录，时长为转出时间减去转入时间
• 若仅有转入State B的记录无转出记录，默认当前时间为2022-11-13 23:51:00，时长为当前时间减去转入时间

预期输出结果：

以下是实现该逻辑的TSQL视图代码：

CREATE VIEW vw_TicketStateBDuration
AS
WITH StateChanges AS (
    -- 提取每条记录的目标状态，并获取下一条变更的时间
    SELECT 
        TicketID,
        时间戳 AS ChangeTime,
        -- 从备注中提取目标状态
        SUBSTRING(备注, CHARINDEX('变更为', 备注) + 3, LEN(备注) - CHARINDEX('变更为', 备注) - 2) AS TargetState,
        -- 获取同工单下的下一条变更时间
        LEAD(时间戳, 1) OVER (PARTITION BY TicketID ORDER BY 时间戳) AS NextChangeTime
    FROM YourTableName -- 替换为实际表名
),
StateBDurations AS (
    -- 筛选转入State B的记录，并计算单次停留时长
    SELECT 
        TicketID,
        DATEDIFF(MINUTE, ChangeTime, 
            -- 若下一条时间为空，使用指定的当前时间；否则使用下一条变更时间
            ISNULL(NextChangeTime, CONVERT(DATETIME, '2022-11-13 23:51:00'))
        ) AS DurationMinutes
    FROM StateChanges
    WHERE TargetState = 'State B'
)
-- 汇总每个工单的总停留时长
SELECT 
    TicketID,
    SUM(DurationMinutes) AS 时长(分钟)
FROM StateBDurations
GROUP BY TicketID;

代码说明 #

1. StateChanges CTE：
   • 从原始表中提取工单ID、变更时间，并从备注字段中解析出目标状态
   • 使用LEAD()函数获取同工单下的下一条状态变更时间，用于计算停留时长
2. StateBDurations CTE：
   • 筛选出所有目标状态为State B的记录（即转入State B的操作）
   • 计算单次停留时长：若存在下一条变更时间，则用下一条时间减去当前变更时间；若不存在，则使用指定的当前时间2022-11-13 23:51:00计算
3. 最终汇总：按工单ID分组，求和得到每个工单在State B的总停留时长

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