SQL自定义列：按数值区间做减法以排除周末计算平均datediff

为什么固定区间减数值的思路不靠谱 #

你想按7-13天减2、14-20天减4的方法有个致命问题：实际包含的周末数量根本不是固定的。比如同样是8天的区间，从周一到下周二只占1个周末（2天），但从周六到下周日会占2个周末（4天），固定减2肯定会出错。

正确的实现方式 #

方法1：直接算工作日数（最推荐） #

不用先算总天数再调整，直接通过计算区间内的周末总数，用总天数减去它就行。

以SQL Server为例，假设你有StartDate和EndDate两个日期列：

SELECT
    StartDate,
    EndDate,
    -- 先算总天数（包含首尾两天）
    DATEDIFF(day, StartDate, EndDate) + 1 AS TotalDays,
    -- 工作日数 = 总天数 - 周末天数
    DATEDIFF(day, StartDate, EndDate) + 1 
    - (DATEDIFF(week, StartDate, EndDate) * 2)
    -- 单独调整首尾如果是周末的情况
    - CASE WHEN DATEPART(weekday, StartDate) = 1 THEN 1 ELSE 0 END
    - CASE WHEN DATEPART(weekday, EndDate) = 7 THEN 1 ELSE 0 END
    AS WorkDays
FROM YourTable;

注意：DATEPART(weekday, ...)的返回值取决于SQL Server的SET DATEFIRST设置，默认周日是1，周六是7。如果你的数据库设置是周一为1，记得修改CASE里的判断值。

方法2：基于已有的TimeToInspect计算 #

如果TimeToInspect已经是总天数（比如DATEDIFF(day, StartDate, EndDate)+1的结果），可以结合起始日期算要减的周末数：

SELECT
    TimeToInspect,
    StartDate,
    TimeToInspect
    - (DATEDIFF(week, StartDate, DATEADD(day, TimeToInspect - 1, StartDate)) * 2)
    - CASE WHEN DATEPART(weekday, StartDate) = 1 THEN 1 ELSE 0 END
    - CASE WHEN DATEPART(weekday, DATEADD(day, TimeToInspect - 1, StartDate)) = 7 THEN 1 ELSE 0 END
    AS WorkDays
FROM YourTable;

方法3：MySQL版本的实现 #

如果用MySQL，用WEEKDAY()函数（周一为0，周日为6）：

SELECT
    StartDate,
    EndDate,
    DATEDIFF(EndDate, StartDate) + 1
    - FLOOR(DATEDIFF(EndDate, StartDate) / 7) * 2
    - CASE WHEN WEEKDAY(StartDate) = 6 THEN 1 ELSE 0 END
    - CASE WHEN WEEKDAY(EndDate) = 5 THEN 1 ELSE 0 END
    AS WorkDays
FROM your_table;

非要用区间调整的话（不推荐） #

如果你的场景是所有日期区间都是完整的整周，那可以试试正确的CASE语法，可能你之前写错了：

SELECT
    TimeToInspect,
    CASE
        WHEN TimeToInspect BETWEEN 7 AND 13 THEN TimeToInspect - 2
        WHEN TimeToInspect BETWEEN 14 AND 20 THEN TimeToInspect - 4
        WHEN TimeToInspect BETWEEN 21 AND 27 THEN TimeToInspect - 6
        ELSE TimeToInspect -- 其他天数的处理逻辑
    END AS AdjustedDays
FROM YourTable;

但还是提醒：只要区间不是完美整周，这个结果就会不准。

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