如何计算邮件接收与回复的有效工作时长（排除无工作记录日期）并验证是否满足24小时响应要求？

首先肯定你的方向：通过视图标记实际有工时的日期来定义工作日，这个思路非常贴合业务场景——毕竟周末加班、节假日休息这类特殊情况，用固定的周一到周五规则根本覆盖不了，你的方案完全能解决这个问题。

接下来直接说怎么计算TotalTimeWithoutDaysNotWorked，我会结合你的示例拆解逻辑，再给出可落地的SQL代码。

核心计算逻辑 #

我们需要分4部分调整总时长：

1. 先算出邮件从收到到回复的原始总时长（就是你示例里的TotalTime）；
2. 扣除接收和回复日期之间的完整非工作日（这些日期要全额扣24小时/天）；
3. 如果接收当天是非工作日，扣除「接收时间到当天结束」的时长；
4. 如果回复当天是非工作日，扣除「当天开始到回复时间」的时长；

最终有效时长 = 原始总时长 - 完整非工作日扣减时长 - 接收日非工作时长 - 回复日非工作时长

用你的示例4验证逻辑 #

接收时间：2021-04-04 11:00，回复时间：2021-04-05 18:00

1. 原始总时长：DATEDIFF(HOUR, '2021-04-04 11:00', '2021-04-05 18:00') = 31小时；
2. 中间没有完整非工作日（4号到5号之间没有其他日期），扣0；
3. 接收当天（4号）HasWorked=0，扣除24 - 11 =13小时；
4. 回复当天（5号）HasWorked=1，扣0；
5. 有效时长：31-13=18，完全匹配你要的结果。

SQL实现方案 #

假设你的工时视图叫v_WorkDays，邮件表叫Emails，我们用CTE分步计算，逻辑更清晰：

WITH EmailBaseInfo AS (
    SELECT
        MailIncoming,
        MailAnswering,
        -- 计算原始总时长（保留小数，兼容分钟级计算）
        DATEDIFF(MINUTE, MailIncoming, MailAnswering)/60.0 AS TotalTime,
        -- 提取日期部分，方便关联工时视图
        CAST(MailIncoming AS DATE) AS IncomingDate,
        CAST(MailAnswering AS DATE) AS AnsweringDate
    FROM Emails
),
WorkDayValidation AS (
    SELECT
        ebi.*,
        -- 接收当天是否有工时（视图没数据则视为无工时）
        COALESCE(wi.HasWorked, 0) AS IncomingHasWorked,
        -- 回复当天是否有工时
        COALESCE(wa.HasWorked, 0) AS AnsweringHasWorked,
        -- 统计中间的完整非工作日数量
        (SELECT COUNT(*) 
         FROM v_WorkDays wd
         WHERE wd.DateWorked BETWEEN DATEADD(DAY, 1, ebi.IncomingDate) 
                                 AND DATEADD(DAY, -1, ebi.AnsweringDate)
           AND wd.HasWorked = 0) AS NonWorkDaysCount
    FROM EmailBaseInfo ebi
    LEFT JOIN v_WorkDays wi ON wi.DateWorked = ebi.IncomingDate
    LEFT JOIN v_WorkDays wa ON wa.DateWorked = ebi.AnsweringDate
)
SELECT
    MailIncoming,
    MailAnswering,
    ROUND(TotalTime, 2) AS TotalTime,
    -- 计算最终有效时长
    ROUND(
        TotalTime
        -- 扣除完整非工作日的时长
        - (NonWorkDaysCount * 24)
        -- 扣除接收日非工作时长（非工作日才扣）
        - CASE WHEN IncomingHasWorked = 0 THEN 
            (24 - DATEPART(HOUR, MailIncoming) - DATEPART(MINUTE, MailIncoming)/60.0) 
          ELSE 0 END
        -- 扣除回复日非工作时长（非工作日才扣）
        - CASE WHEN AnsweringHasWorked = 0 THEN 
            (DATEPART(HOUR, MailAnswering) + DATEPART(MINUTE, MailAnswering)/60.0) 
          ELSE 0 END,
        2
    ) AS TotalTimeWithoutDaysNotWorked
FROM WorkDayValidation;

代码关键点说明 #

• 用DATEDIFF(MINUTE, ...)/60.0计算时长，支持分钟级的精确统计（比如12:30会算成12.5小时）；
• COALESCE处理视图中缺失的日期：如果某个日期没有工时记录，视图里可能没有这条数据，LEFT JOIN后会返回NULL，用COALESCE转成0代表无工时；
• ROUND(...,2)是为了让结果更美观，你可以根据需求调整小数位数。

内容的提问来源于stack exchange，提问作者Sonny S.