如何优化计算办公时段及排除公假日的TAT工时SQL查询

需求说明 #

• 仅统计**办公时段（09:00-18:00）**的有效工时
• 若任务结束时间晚于18:00，超出部分顺延至次日09:00计算
• 排除公共假日，假日期间的工时顺延至假日次日

原有可运行查询 #

DECLARE @StartDate DATETIME, @EndDate DATETIME;

SELECT @StartDate = MIN(StartTime), @EndDate = MAX(EndTime) 
FROM Tasks1;

WITH Calendar AS 
(
    SELECT CONVERT(date, @StartDate) AS Date

    UNION ALL

    SELECT DATEADD(day, 1, Date) FROM Calendar
    WHERE Date < CONVERT(date, @EndDate)
)
SELECT DISTINCT
    TaskID,
    (CASE 
         WHEN DATEPART(HOUR, StartTime) >= 9 AND DATEPART(HOUR, 
         EndTime) <= 18 AND DATEPART(WEEKDAY, EndTime) BETWEEN 2 
         AND 6 THEN DATEDIFF(HOUR, StartTime, EndTime)

         WHEN DATEPART(HOUR, StartTime) >= 9 
         AND DATEPART(WEEKDAY, StartTime) BETWEEN 2 AND 6 
         THEN DATEDIFF(HOUR, StartTime, CAST(CONVERT(datetime, 
         CONVERT(date, StartTime)) + ' 18:00:00' AS datetime))
         WHEN DATEPART(HOUR, EndTime) <= 18 AND 
         DATEPART(WEEKDAY,EndTime) BETWEEN 2 AND 6  
         THEN DATEDIFF(HOUR, CAST(CONVERT(date, EndTime) AS 
         datetime) + ' 09:00:00', EndTime)

         ELSE
        CASE WHEN CONVERT(date, StartTime) = CONVERT(date,EndTime) 
        THEN DATEDIFF(HOUR, CAST(CONVERT(date, EndTime) AS 
        datetime) + ' 09:00:00', EndTime)

        ELSE DATEDIFF(HOUR, CAST(CONVERT(date, Calendar.Date) AS 
        datetime) + ' 09:00:00', EndTime) END END) AS 
        Working_Hours
FROM 
    Tasks1
JOIN 
    Calendar ON Calendar.Date BETWEEN CAST(CONVERT(date, StartTime) AS datetime) 
             AND CAST(CONVERT(date, EndTime) AS datetime)
LEFT JOIN 
    PublicHolidays ON Calendar.Date = CONVERT(date, PublicHolidays.HolidayDate)
WHERE 
    PublicHolidays.HolidayDate IS NULL 
    OR DATEPART(WEEKDAY, PublicHolidays.HolidayDate) BETWEEN 2 AND 6;

优化后的查询 #

-- 定义办公时段常量，便于后续维护修改
DECLARE @WorkStart TIME = '09:00:00';
DECLARE @WorkEnd TIME = '18:00:00';
DECLARE @FullWorkDayHours INT = DATEDIFF(HOUR, @WorkStart, @WorkEnd);

DECLARE @MinTaskDate DATE, @MaxTaskDate DATE;
SELECT @MinTaskDate = CAST(MIN(StartTime) AS DATE), @MaxTaskDate = CAST(MAX(EndTime) AS DATE)
FROM Tasks1;

-- 生成过滤后的工作日历（排除周末和公共假日）
WITH WorkingCalendar AS (
    SELECT @MinTaskDate AS CalendarDate
    UNION ALL
    SELECT DATEADD(DAY, 1, CalendarDate)
    FROM WorkingCalendar
    WHERE CalendarDate < @MaxTaskDate
),
FilteredWorkingDays AS (
    SELECT w.CalendarDate
    FROM WorkingCalendar w
    LEFT JOIN PublicHolidays h ON w.CalendarDate = CAST(h.HolidayDate AS DATE)
    -- 排除周末（注意：DATEPART(WEEKDAY)结果依赖SQL Server的DATEFIRST设置，此处默认周日为1）
    WHERE DATEPART(WEEKDAY, w.CalendarDate) BETWEEN 2 AND 6
      AND h.HolidayDate IS NULL
)
-- 分组计算每个任务的总有效工时
SELECT 
    t.TaskID,
    SUM(
        CASE
            -- 任务起始日：计算当日办公时段内的有效工时
            WHEN fw.CalendarDate = CAST(t.StartTime AS DATE) THEN
                DATEDIFF(MINUTE, 
                    CASE WHEN CAST(t.StartTime AS TIME) < @WorkStart THEN @WorkStart ELSE CAST(t.StartTime AS TIME) END,
                    @WorkEnd
                ) / 60.0
            -- 任务结束日：计算当日办公时段内的有效工时
            WHEN fw.CalendarDate = CAST(t.EndTime AS DATE) THEN
                DATEDIFF(MINUTE, 
                    @WorkStart,
                    CASE WHEN CAST(t.EndTime AS TIME) > @WorkEnd THEN @WorkEnd ELSE CAST(t.EndTime AS TIME) END
                ) / 60.0
            -- 中间工作日：按完整办公时长计算
            ELSE @FullWorkDayHours
        END
    ) AS Total_Working_Hours
FROM Tasks1 t
JOIN FilteredWorkingDays fw 
    ON fw.CalendarDate BETWEEN CAST(t.StartTime AS DATE) AND CAST(t.EndTime AS DATE)
GROUP BY t.TaskID
OPTION (MAXRECURSION 0); -- 时间范围超过100天时需开启此选项

优化点说明 #

• 可维护性提升：将办公时段、时长定义为常量，后续修改无需改动核心逻辑
• 日历表优化：提前过滤周末和公共假日，避免关联时重复判断，提升查询性能
• 逻辑清晰度：将起始日、中间日、结束日的工时计算拆分为独立CASE分支，避免原查询复杂嵌套的逻辑混乱
• 精度优化：使用分钟计算后转为小时，避免整小时截断导致的统计误差
• 统计准确性：移除原查询的DISTINCT，改用GROUP BY+SUM正确计算任务总工时（原查询的DISTINCT会导致工时统计错误）
• 简化转换：使用CAST(xxx AS DATE)替代多层CONVERT，提升代码可读性和执行效率

内容的提问来源于stack exchange，提问作者A.Sri Harsha