SQL Server中计算跨日期时段内的准确休息时长问题

我看了你的SQL查询，发现核心问题出在休息时长的计算逻辑上——你用DATEDIFF(DAY, a.start_date, a.end_date)+1直接乘以单次休息时长，这种方式默认任务每天都完整覆盖了整个休息时段，但当任务结束时间是次日上午（比如第三天9点），最后一天的休息时段根本还没开始，或者任务在首尾两天只覆盖了部分休息时段时，计算结果就会失真。

要解决这个问题，我们需要逐天计算每天实际重叠的休息时长，而不是粗暴地按天数相乘。具体可以用递归CTE生成任务周期内的所有日期，然后对每一天单独计算休息时段和任务时段的交集，最后把所有天的交集时长加起来。

下面是修改后的完整查询，重点优化了休息时长的计算逻辑：

WITH DateRange AS (
    -- 递归生成任务期间的所有日期
    SELECT CAST(a.start_date AS DATE) AS TaskDate
    FROM spms_tblSubTask AS a
    LEFT JOIN pmis.dbo.employee as b ON a.eid = b.eid
    WHERE b.Shift = 0
    UNION ALL
    SELECT DATEADD(DAY, 1, TaskDate)
    FROM DateRange
    WHERE TaskDate < (SELECT CAST(a.end_date AS DATE) FROM spms_tblSubTask AS a LEFT JOIN pmis.dbo.employee as b ON a.eid = b.eid WHERE b.Shift = 0)
)
SELECT 
    FORMAT(CAST(CONCAT(a.start_date,' ',a.start_time) AS DATETIME2), 'MM/dd/yyyy hh:mm:ss tt') AS Start_Time,
    FORMAT(CAST(CONCAT(a.end_date,' ',a.end_time) AS DATETIME2), 'MM/dd/yyyy hh:mm:ss tt') AS End_Time,
    DATEDIFF(MINUTE, CONCAT(a.start_date,' ',a.start_time), CONCAT(a.end_date,' ',a.end_time)) / 60.0 AS TotalTime,
    c.break_from,
    c.break_to,
    -- 计算总休息时长：逐天求和每天的重叠休息分钟数，再转成小时
    ISNULL((SUM(
        DATEDIFF(MINUTE,
            -- 当天休息时段的开始和当天任务开始的较晚者
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2) > CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
            END,
            -- 当天休息时段的结束和当天任务结束的较早者
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2) < CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
            END
        )
        -- 如果重叠时长为负，说明当天没有重叠的休息时段，取0
        * CASE WHEN DATEDIFF(MINUTE,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2) > CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
            END,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2) < CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
            END
        ) > 0 THEN 1 ELSE 0 END
    ) / 60.0), 0) AS TotalBreak,
    ISNULL((DATEDIFF(MINUTE, CAST(CONCAT(a.start_date,' ',a.start_time) AS DATETIME2), CAST(CONCAT(a.end_date,' ',a.end_time) AS DATETIME2))/60.0), 0) AS OriginalTime,
    -- 统一用新逻辑计算Break_字段
    ISNULL((SUM(
        DATEDIFF(MINUTE,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2) > CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
            END,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2) < CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
            END
        )
        * CASE WHEN DATEDIFF(MINUTE,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2) > CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
            END,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2) < CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
            END
        ) > 0 THEN 1 ELSE 0 END
    )), 0) AS Break_,
    -- 用新的休息时长计算实际工作时长
    ISNULL((((DATEDIFF(MINUTE, CAST(CONCAT(a.start_date,' ',a.start_time) AS DATETIME2), CAST(CONCAT(a.end_date,' ',a.end_time) AS DATETIME2))) - ISNULL((SUM(
        DATEDIFF(MINUTE,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2) > CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
            END,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2) < CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
            END
        )
        * CASE WHEN DATEDIFF(MINUTE,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2) > CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_from) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.start_date AS DATE) THEN a.start_time ELSE '00:00:00' END) AS DATETIME2)
            END,
            CASE 
                WHEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2) < CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
                THEN CAST(CONCAT(dr.TaskDate,' ',c.break_to) AS DATETIME2)
                ELSE CAST(CONCAT(CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_date ELSE dr.TaskDate END,' ',CASE WHEN dr.TaskDate = CAST(a.end_date AS DATE) THEN a.end_time ELSE '23:59:59' END) AS DATETIME2)
            END
        ) > 0 THEN 1 ELSE 0 END
    )), 0))/60.0), 0) AS EstimatedTotalWorkHours,
    ISNULL(c.code, 'TIME14') AS TimeReference
FROM spms_tblSubTask AS a
LEFT JOIN pmis.dbo.employee as b ON a.eid = b.eid
LEFT JOIN pmis.dbo.time_reference as c ON c.code = ISNULL(b.TimeReference, 'TIME14')
JOIN DateRange dr ON dr.TaskDate BETWEEN CAST(a.start_date AS DATE) AND CAST(a.end_date AS DATE)
WHERE b.Shift = 0
GROUP BY a.start_date, a.start_time, a.end_date, a.end_time, c.break_from, c.break_to, c.code, b.TimeReference
OPTION (MAXRECURSION 0); -- 如果任务跨超过100天，必须开启这个选项

关键逻辑说明： #

1. 递归CTE DateRange：生成任务从开始到结束的所有日期，确保我们能逐天处理每个时段。
2. 每天的任务窗口计算：
   • 第一天的任务起始时间用a.start_time，其他天默认从当天0点开始；
   • 最后一天的任务结束时间用a.end_time，其他天默认到当天23:59:59结束。
3. 休息时段与任务窗口的交集计算：
   • 取休息时段开始和任务窗口开始的较晚值作为交集起始点；
   • 取休息时段结束和任务窗口结束的较早值作为交集结束点；
   • 如果交集起始点晚于结束点，说明当天没有重叠的休息时长，直接取0。
4. 求和所有天的交集时长：把每天的有效休息分钟数累加，再转换成小时，得到准确的总休息时长。

这样修改后，不管任务是跨1天、2天还是N天，只要结束时间在某天上午，都会准确计算实际覆盖的休息时段，不会多算未发生的休息时间。

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