求助：SQL计算员工上月BusinessTime内的休假时长

需求 #

统计员工上月处于**BusinessTime（工作时段）**内的休假时长。示例：员工周五8:00至下周一17:00休假，按周一至周五8:00-17:00的工作规则，休假时长计为18小时。

现有数据 #

TechTimeOff表 #

记录员工上月的休假实例，需计算DurationOff列（分钟或小数形式）：

Calendar表 #

记录上月每一分钟的信息，非工作时段的BusinessTime标记为'No'：

原思路问题 #

想将TechTimeOff表的休假时段拆分为每分钟一行，再关联Calendar表过滤非工作时段，但不知如何实现时段拆分。

最优解决方案 #

无需拆分休假时段，直接利用Calendar表的分钟数据关联统计，效率更高：

SQL实现代码 #

SELECT
    t.TechID,
    t.TechName,
    t.TimeOff_ID,
    t.TimeOff_From,
    t.TimeOff_To,
    COUNT(c.MinuteOfTheMonth) AS TotalMinutesOff,
    ROUND(COUNT(c.MinuteOfTheMonth) / 60.0, 2) AS DurationOff_Hrs
FROM TechTimeOff t
JOIN Calendar c
    ON c.MinuteOfTheMonth BETWEEN t.[From(MinuteOfTheMonth)] AND t.[To(MinuteOfTheMonth)]
    AND c.BusinessTime = 'Yes'
GROUP BY t.TechID, t.TechName, t.TimeOff_ID, t.TimeOff_From, t.TimeOff_To
ORDER BY t.TechID;

方案说明 #

1. 通过JOIN关联两张表，筛选出休假时段内属于工作时段的所有分钟记录
2. 按员工及休假实例分组，统计符合条件的分钟总数
3. 将分钟数转换为小时数（保留两位小数），得到最终的工作时段内休假时长

备选拆分时段方案（不推荐） #

如果一定要拆分休假时段到每分钟，可使用递归CTE实现，但会生成大量行，性能较差：

WITH TimeOffMinutes AS (
    SELECT
        TechID,
        TechName,
        TimeOff_ID,
        [From(MinuteOfTheMonth)] AS CurrentMinute,
        [To(MinuteOfTheMonth)] AS EndMinute
    FROM TechTimeOff
    UNION ALL
    SELECT
        TechID,
        TechName,
        TimeOff_ID,
        CurrentMinute + 1,
        EndMinute
    FROM TimeOffMinutes
    WHERE CurrentMinute < EndMinute
)
SELECT
    tom.TechID,
    tom.TechName,
    tom.TimeOff_ID,
    COUNT(c.MinuteOfTheMonth) AS TotalMinutesOff,
    ROUND(COUNT(c.MinuteOfTheMonth) / 60.0, 2) AS DurationOff_Hrs
FROM TimeOffMinutes tom
JOIN Calendar c
    ON c.MinuteOfTheMonth = tom.CurrentMinute
    AND c.BusinessTime = 'Yes'
GROUP BY tom.TechID, tom.TechName, tom.TimeOff_ID
ORDER BY tom.TechID
OPTION (MAXRECURSION 0); -- 需开启无限递归，避免时段过长报错

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