SQL Server中实现签到签退时长的时分秒正确汇总求和

你已经通过LEAD和PARTITION BY成功分离了同一字段中的签到（CHECKIN）和签退（CHECKOUT）时间，但当前拼接的CONCATEADO字段未实现秒、分的自动进位，且无法直接汇总。以下是修正方案：

核心思路 #

1. 先计算每条签到签退记录的总秒数差值，避免时分秒单独计算导致的进位问题
2. 将总秒数转换为HH:MM:SS标准格式，自动处理进位
3. 汇总时直接对总秒数求和，再转换为标准时长格式

修正后的查询语句 #

WITH T_REPRES AS (
    SELECT USU.CODIGO_ACESSO, USU.USUARIO, USU.CODIGO
    FROM DB_USUARIO USU
    -- WHERE USU.NIVEL_REPRESENTANTE IN (@IDESTRUTURA)
),
T_EVENTOS_PAIRED AS (
    SELECT 
        T1.USUARIO,
        T1.DATA AS CHECKIN_TIME,
        T1.FUNCIONALIDADE,
        LEAD(T1.DATA) OVER (
            PARTITION BY T1.USUARIO, CAST(T1.DATA AS DATE) 
            ORDER BY T1.DATA
        ) AS CHECKOUT_TIME,
        -- 计算单条记录的总秒数时长（CHECKOUT - CHECKIN）
        DATEDIFF(SECOND, T1.DATA, LEAD(T1.DATA) OVER (
            PARTITION BY T1.USUARIO, CAST(T1.DATA AS DATE) 
            ORDER BY T1.DATA
        )) AS DURATION_SECONDS
    FROM (
        SELECT REP.USUARIO, TE.DATA, TE.FUNCIONALIDADE, TE.CODIGO_CLIENTE
        FROM DB_MOB_TIMELINE_EVENTOS TE
        JOIN T_REPRES REP ON TE.USUARIO = REP.USUARIO
        WHERE TE.FUNCIONALIDADE IN ('CHECKIN', 'CHECKOUT')
    ) T1
)
-- 1. 查看单条记录的标准时长格式
SELECT 
    USUARIO,
    CAST(CHECKIN_TIME AS DATE) AS PERIODO,
    FUNCIONALIDADE,
    CHECKIN_TIME,
    CHECKOUT_TIME,
    -- 将总秒数转换为HH:MM:SS格式
    FORMAT(DURATION_SECONDS / 3600, '00') + ':' +
    FORMAT((DURATION_SECONDS % 3600) / 60, '00') + ':' +
    FORMAT(DURATION_SECONDS % 60, '00') AS DURATION_FORMATTED
FROM T_EVENTOS_PAIRED
WHERE CHECKOUT_TIME IS NOT NULL
  AND CAST(CHECKIN_TIME AS DATE) = '2023-04-17'

-- 2. 按用户汇总总时长（取消注释即可使用）
/*
SELECT 
    USUARIO,
    CAST(CHECKIN_TIME AS DATE) AS PERIODO,
    FORMAT(SUM(DURATION_SECONDS) / 3600, '00') + ':' +
    FORMAT((SUM(DURATION_SECONDS) % 3600) / 60, '00') + ':' +
    FORMAT(SUM(DURATION_SECONDS) % 60, '00') AS TOTAL_DURATION
FROM T_EVENTOS_PAIRED
WHERE CHECKOUT_TIME IS NOT NULL
  AND CAST(CHECKIN_TIME AS DATE) = '2023-04-17'
GROUP BY USUARIO, CAST(CHECKIN_TIME AS DATE)
*/

关键说明 #

• 替换了原CONCATEADO的拼接逻辑，改用总秒数转换，自动处理秒满60进分、分满60进小时的进位规则
• 新增DURATION_SECONDS字段，后续汇总时直接对该字段求和即可，避免对字符串格式的时长进行复杂计算
• 使用FORMAT函数保证时分秒均为两位数字（不足补0），符合标准格式要求

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