如何计算员工班次间隔休息时长？存储过程优化咨询

需求说明 #

需要计算员工班次之间的休息时长：从数据库的考勤表（LogData）和工作任务表（BookingSessions）中，找出员工每日最早的考勤或工作任务开始时间（即当日班次开始时间），并计算该时间与前一日最晚的考勤或工作任务结束时间（即前一班次结束时间）的间隔小时数。

当前存储过程 #

PROCEDURE [dbo].[ReportEmployeeRestHours] 
    -- Add the parameters for the stored procedure here
    @dtFrom DATETIME,
    @dtTo DATETIME
AS
BEGIN
    SELECT
    EmployeeName,
    WorkerReferenceID,
    WorkDate,
    MIN(BookingStart) AS MinBookingStartTime,
    MAX(BookingEnd) AS MaxBookingEndTime,
    MIN(LogStart) AS MinLogStartTime,
    MAX(LogEnd) AS MaxLogEndTime,
    ISNULL(
        CASE
            WHEN MIN(BookingStart) IS NOT NULL AND (MIN(LogStart) IS NULL OR MIN(BookingStart) < MIN(LogStart)) THEN MIN(BookingStart)
            ELSE MIN(LogStart)
        END, MIN(BookingStart)
    ) AS MinStartTime,
    ISNULL(
        CASE
            WHEN MAX(BookingEnd) IS NOT NULL AND (MAX(LogEnd) IS NULL OR MAX(BookingEnd) > MAX(LogEnd)) THEN MAX(BookingEnd)
            ELSE MAX(LogEnd)
        END, MAX(BookingEnd)
    ) AS MaxEndTime
FROM (
    
    SELECT
            Employees.Forename + ' ' + Employees.Surname AS EmployeeName,
            Employees.WorkerReferenceID,
            BookingSessions.JobStartDT AS WorkDate,
            -- Gets the date from the Job Start. Gets the time from the employee start time/ end time. Combines them.
            CONVERT(datetime, BookingSessions.JobStartDT + ' ' + CONVERT(varchar, BookingSessionEmployees.StartTime, 108)) AS BookingStart,
            CONVERT(datetime, BookingSessions.JobStartDT + ' ' + CONVERT(varchar, BookingSessionEmployees.EndTime, 108)) AS BookingEnd,
            NULL AS LogStart,
            NULL AS LogEnd
        FROM Employees
        INNER JOIN BookingSessionEmployees ON Employees.EmployeeID = BookingSessionEmployees.EmployeeID
        INNER JOIN BookingSessionLocationTasks ON BookingSessionEmployees.BookingSessionLocationTaskID = BookingSessionLocationTasks.BookingSessionLocationTaskID
        INNER JOIN BookingSessions ON BookingSessionLocationTasks.BookingSessionLocationID = BookingSessions.BookingSessionID
        WHERE BookingSessions.JobStartDT BETWEEN @dtFrom AND @dtTo
              AND BookingSessionEmployees.StartTime IS NOT NULL
              AND BookingSessionEmployees.EndTime IS NOT NULL

    UNION

    SELECT
    Employees.Forename + ' ' + Employees.Surname AS EmployeeName,
    Employees.WorkerReferenceID,
    LogData.DateOfWork AS WorkDate,
    NULL AS BookingStart,
    NULL AS BookingEnd,
    -- Gets the date from the date of work. Gets the time from the start time. Combines them. 
    CONVERT(datetime, LogData.DateOfWork + ' ' + CONVERT(varchar, LogData.StartTime, 108)) AS LogStart,
    CONVERT(datetime, LogData.DateOfWork + ' ' + CONVERT(varchar, LogData.EndTime, 108)) AS LogEnd
    FROM Employees
    INNER JOIN LogData ON Employees.EmployeeID = LogData.EmployeeID
    WHERE LogData.DateOfWork BETWEEN @dtFrom AND @dtTo
          AND LogData.StartTime IS NOT NULL
          AND LogData.EndTime IS NOT NULL
          AND LogData.StartTime <> LogData.EndTime -- Exclude entries where StartTime equals EndTime because these are all bonus Timesheets
) AS CombinedData
GROUP BY EmployeeName, WorkerReferenceID, WorkDate;
END

当前问题 #

无法筛选出每日的最早开始时间，会返回当日多条记录；且存储过程逻辑复杂，可读性和效率有待提升。

优化思路与方案 #

1. 提前聚合数据，减少计算量 #

先分别对BookingSessions和LogData按员工+日期聚合，得到每日的最早/最晚时间，再合并数据集，避免UNION后大量数据的重复计算。

2. 简化起止时间判断逻辑 #

用更简洁的条件判断替代嵌套的ISNULL+CASE，提升代码可读性；SQL Server 2022及以上版本可直接使用LEAST/GREATEST函数，低版本用CASE简化。

3. 用窗口函数计算跨日休息时长 #

使用LAG()窗口函数按员工分组、日期排序，直接获取前一日的最晚结束时间，再通过DATEDIFF计算间隔小时数。

优化后的存储过程 #

PROCEDURE [dbo].[ReportEmployeeRestHours] 
    @dtFrom DATETIME,
    @dtTo DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    -- 聚合工作任务的每日起止时间
    WITH BookingDaily AS (
        SELECT
            e.Forename + ' ' + e.Surname AS EmployeeName,
            e.WorkerReferenceID,
            bs.JobStartDT AS WorkDate,
            MIN(CONVERT(DATETIME, bs.JobStartDT + ' ' + CONVERT(VARCHAR, bse.StartTime, 108))) AS BookingMinStart,
            MAX(CONVERT(DATETIME, bs.JobStartDT + ' ' + CONVERT(VARCHAR, bse.EndTime, 108))) AS BookingMaxEnd
        FROM Employees e
        JOIN BookingSessionEmployees bse ON e.EmployeeID = bse.EmployeeID
        JOIN BookingSessionLocationTasks bslt ON bse.BookingSessionLocationTaskID = bslt.BookingSessionLocationTaskID
        JOIN BookingSessions bs ON bslt.BookingSessionLocationID = bs.BookingSessionID
        WHERE bs.JobStartDT BETWEEN @dtFrom AND @dtTo
          AND bse.StartTime IS NOT NULL
          AND bse.EndTime IS NOT NULL
        GROUP BY e.Forename + ' ' + e.Surname, e.WorkerReferenceID, bs.JobStartDT
    ),
    -- 聚合考勤数据的每日起止时间
    LogDaily AS (
        SELECT
            e.Forename + ' ' + e.Surname AS EmployeeName,
            e.WorkerReferenceID,
            ld.DateOfWork AS WorkDate,
            MIN(CONVERT(DATETIME, ld.DateOfWork + ' ' + CONVERT(VARCHAR, ld.StartTime, 108))) AS LogMinStart,
            MAX(CONVERT(DATETIME, ld.DateOfWork + ' ' + CONVERT(VARCHAR, ld.EndTime, 108))) AS LogMaxEnd
        FROM Employees e
        JOIN LogData ld ON e.EmployeeID = ld.EmployeeID
        WHERE ld.DateOfWork BETWEEN @dtFrom AND @dtTo
          AND ld.StartTime IS NOT NULL
          AND ld.EndTime IS NOT NULL
          AND ld.StartTime <> ld.EndTime
        GROUP BY e.Forename + ' ' + e.Surname, e.WorkerReferenceID, ld.DateOfWork
    ),
    -- 合并数据并计算当日最早/最晚时间
    DailyAgg AS (
        SELECT
            COALESCE(b.EmployeeName, l.EmployeeName) AS EmployeeName,
            COALESCE(b.WorkerReferenceID, l.WorkerReferenceID) AS WorkerReferenceID,
            COALESCE(b.WorkDate, l.WorkDate) AS WorkDate,
            -- 当日最早开始时间
            COALESCE(
                CASE 
                    WHEN b.BookingMinStart < l.LogMinStart THEN b.BookingMinStart 
                    ELSE l.LogMinStart 
                END,
                b.BookingMinStart,
                l.LogMinStart
            ) AS DailyMinStart,
            -- 当日最晚结束时间
            COALESCE(
                CASE 
                    WHEN b.BookingMaxEnd > l.LogMaxEnd THEN b.BookingMaxEnd 
                    ELSE l.LogMaxEnd 
                END,
                b.BookingMaxEnd,
                l.LogMaxEnd
            ) AS DailyMaxEnd
        FROM BookingDaily b
        FULL JOIN LogDaily l 
            ON b.WorkerReferenceID = l.WorkerReferenceID 
            AND b.WorkDate = l.WorkDate
    )
    -- 最终结果，计算跨日休息时长
    SELECT
        EmployeeName,
        WorkerReferenceID,
        WorkDate,
        DailyMinStart,
        DailyMaxEnd,
        DATEDIFF(HOUR, LAG(DailyMaxEnd) OVER (PARTITION BY WorkerReferenceID ORDER BY WorkDate), DailyMinStart) AS RestHours
    FROM DailyAgg
    ORDER BY WorkerReferenceID, WorkDate;
END

优化效果说明 #

• 提前聚合数据：减少中间数据集的大小，提升查询效率
• FULL JOIN确保不会遗漏只有工作任务或只有考勤记录的日期
• 窗口函数LAG()简化跨日数据关联，直接获取前一日的结束时间
• 逻辑分层清晰，代码可读性大幅提升

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