如何用SQL计算工时表各活动的开始/结束时间？求助实现方案

假设你的工时表表结构包含以下字段：EmployeeID（员工ID）、WorkDate（工作日期）、ActivityOrder（活动顺序）、StartDateTime（仅首个活动有值）、DurationMinutes（活动耗时分钟数）。以下是两种可行的实现方法：

方法一：窗口函数（推荐） #

利用窗口函数的累加计算能力，无需自连接就能高效处理，还能兼容ActivityOrder不连续的场景：

SELECT
    EmployeeID,
    WorkDate,
    ActivityOrder,
    -- 计算当前活动的开始时间
    COALESCE(
        StartDateTime,
        DATEADD(MINUTE, 
            SUM(DurationMinutes) OVER (
                PARTITION BY EmployeeID, WorkDate 
                ORDER BY ActivityOrder 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ),
            FIRST_VALUE(StartDateTime) OVER (PARTITION BY EmployeeID, WorkDate ORDER BY ActivityOrder)
        )
    ) AS StartDateTime,
    -- 计算当前活动的结束时间
    DATEADD(MINUTE, 
        DurationMinutes,
        COALESCE(
            StartDateTime,
            DATEADD(MINUTE, 
                SUM(DurationMinutes) OVER (
                    PARTITION BY EmployeeID, WorkDate 
                    ORDER BY ActivityOrder 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                ),
                FIRST_VALUE(StartDateTime) OVER (PARTITION BY EmployeeID, WorkDate ORDER BY ActivityOrder)
            )
        )
    ) AS EndDateTime,
    DurationMinutes
FROM time_sheet
ORDER BY EmployeeID, WorkDate, ActivityOrder;

逻辑说明： #

• FIRST_VALUE(StartDateTime) 获取每个员工每日首个活动的开始时间，作为后续活动时间计算的基准
• SUM(DurationMinutes) OVER (...) 累加当前活动之前所有活动的总耗时，用基准时间加上这个累加值得到当前活动的开始时间
• COALESCE 保留首个活动的原始开始时间，避免覆盖

方法二：修正后的自连接方案 #

你之前的自连接失败大概率是因为未按员工和日期分组，或仅用ActivityOrder +1关联导致遗漏（比如ActivityOrder不连续）。以下是修正后的写法：

SELECT
    t1.EmployeeID,
    t1.WorkDate,
    t1.ActivityOrder,
    CASE 
        WHEN t1.ActivityOrder = 1 THEN t1.StartDateTime
        ELSE DATEADD(MINUTE, t2.TotalDuration, t_first.StartDateTime)
    END AS StartDateTime,
    CASE 
        WHEN t1.ActivityOrder = 1 THEN DATEADD(MINUTE, t1.DurationMinutes, t1.StartDateTime)
        ELSE DATEADD(MINUTE, t2.TotalDuration + t1.DurationMinutes, t_first.StartDateTime)
    END AS EndDateTime,
    t1.DurationMinutes
FROM time_sheet t1
-- 关联每个员工每日的首个活动开始时间
LEFT JOIN (
    SELECT EmployeeID, WorkDate, StartDateTime 
    FROM time_sheet 
    WHERE ActivityOrder = 1
) t_first 
    ON t1.EmployeeID = t_first.EmployeeID 
    AND t1.WorkDate = t_first.WorkDate
-- 累加当前活动之前所有活动的总耗时
LEFT JOIN (
    SELECT 
        t2.EmployeeID,
        t2.WorkDate,
        t2.ActivityOrder,
        SUM(t3.DurationMinutes) AS TotalDuration
    FROM time_sheet t2
    JOIN time_sheet t3 
        ON t2.EmployeeID = t3.EmployeeID 
        AND t2.WorkDate = t3.WorkDate 
        AND t3.ActivityOrder < t2.ActivityOrder
    GROUP BY t2.EmployeeID, t2.WorkDate, t2.ActivityOrder
) t2 
    ON t1.EmployeeID = t2.EmployeeID 
    AND t1.WorkDate = t2.WorkDate 
    AND t1.ActivityOrder = t2.ActivityOrder
ORDER BY t1.EmployeeID, t1.WorkDate, t1.ActivityOrder;

原自连接失败的原因： #

1. 未按EmployeeID和WorkDate分组关联，导致跨员工/跨日期匹配活动
2. 仅用t1.ActivityOrder = t2.ActivityOrder +1关联，若ActivityOrder存在断层（比如跳过某序号），会直接遗漏后续活动的时间计算

内容的提问来源于stack exchange，提问作者opperman.eric