如何编写SQL查询基于单datetime列统计工单排除指定状态的实际耗时

需求说明 #

统计工单排除onHold、Waiting for customer、Resolved、Closed四个状态外的总停留时长，即工单实际有效处理耗时。
原问题使用的表结构如下：

问题分析 #

你原有CROSS APPLY方案只返回第一个符合条件状态耗时的核心原因是，TICKETHISTORYID的排序可能和状态变更时间TICKETTIME排序不一致，导致匹配到错误的下一条状态记录。我们可以使用更简洁高效的LEAD窗口函数来实现需求。

最终实现代码 #

WITH TicketStatusInterval AS (
    SELECT
        TICKETNUMBER,
        CURRENTSTATUS_ANALYST,
        TICKETTIME,
        -- 按工单分组，取当前状态的下一次变更时间
        LEAD(TICKETTIME) OVER(PARTITION BY TICKETNUMBER ORDER BY TICKETTIME) AS NextChangeTime
    FROM [Admin].[TbtrnTicketHistory]
    WHERE TICKETNUMBER = '211135'
)
SELECT
    TICKETNUMBER,
    -- 若当前是最新状态，需要统计到当前时间可以把NextChangeTime替换为ISNULL(NextChangeTime, GETDATE())
    SUM(DATEDIFF(MINUTE, TICKETTIME, NextChangeTime)) AS TotalValidMinutes
FROM TicketStatusInterval
-- 过滤排除指定状态
WHERE CURRENTSTATUS_ANALYST NOT IN ('On-Hold','Waiting For Customer','Resolved','Closed')
GROUP BY TICKETNUMBER

CROSS APPLY方案修正版 #

如果你需要沿用原有CROSS APPLY写法，可调整为以下版本：

SELECT 
    t1.TICKETNUMBER,
    SUM(DATEDIFF(MINUTE, t1.TICKETTIME, t2.CloseTime)) AS TotalMinutes
FROM [Admin].[TbtrnTicketHistory] t1
CROSS APPLY(
    SELECT TOP 1 TICKETTIME AS CloseTime
    FROM [Admin].[TbtrnTicketHistory] t2
    WHERE t1.TICKETNUMBER = t2.TICKETNUMBER
    AND t2.TICKETTIME > t1.TICKETTIME
    ORDER BY t2.TICKETTIME ASC
) t2
WHERE t1.CURRENTSTATUS_ANALYST NOT IN ('On-Hold','Waiting For Customer','Resolved','Closed')
AND t1.ticketnumber = '211135'
GROUP BY t1.TICKETNUMBER

注：该版本将原关联条件从TICKETHISTORYID比较改为TICKETTIME比较，避免ID和时间排序不一致导致的匹配错误

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