如何用datediff排除日期范围周末？GRM续签工作日统计问题

看起来你已经搞定了单条记录的工作日计算，现在卡在两个关键点：自动定位上月数据，以及把工作日统计逻辑和这个时间范围结合起来算出平均值对吧？我来给你拆解一下解决方案，分步骤来：

第一步：自动获取上月的时间范围 #

不同数据库的日期函数不一样，我给你列几个常用的写法，直接套就行：

• MySQL：
  • 上月第一天：DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
  • 上月最后一天：LAST_DAY(NOW() - INTERVAL 1 MONTH)
• PostgreSQL：
  • 上月第一天：DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')::DATE
  • 上月最后一天：(DATE_TRUNC('month', CURRENT_DATE)::DATE - INTERVAL '1 day')::DATE
• SQL Server：
  • 上月第一天：DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0)
  • 上月最后一天：DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

第二步：结合工作日统计与上月数据筛选 #

假设你的续签表是ra_renewals，其中：

• completion_date是续签完成时间
• grm_start_date和grm_end_date是你要计算天数的xx和yy
• 还有个company_holidays表存节假日（字段holiday_date）

我以MySQL为例写完整查询，你可以根据自己的数据库调整：

SELECT AVG(calculated_work_days) AS avg_grm_renewal_work_days
FROM (
    SELECT
        -- 计算两个日期之间的工作日数：总天数 - 周末天数 - 工作日节假日数
        DATEDIFF(r.grm_end_date, r.grm_start_date) + 1
        -- 减去整周的周末天数
        - FLOOR((DATEDIFF(r.grm_end_date, r.grm_start_date) + 1 + WEEKDAY(r.grm_start_date)) / 7) * 2
        -- 减去首尾不完整周的周末天数
        - CASE 
            WHEN WEEKDAY(r.grm_start_date) = 6 THEN 1 -- 开始是周日
            WHEN WEEKDAY(r.grm_end_date) = 5 THEN 1 -- 结束是周六
            ELSE 0
          END
        -- 减去这段时间内的工作日节假日（周末的节假日已经算在周末里了）
        - (SELECT COUNT(*) 
           FROM company_holidays h
           WHERE h.holiday_date BETWEEN r.grm_start_date AND r.grm_end_date
           AND WEEKDAY(h.holiday_date) < 5) AS calculated_work_days
    FROM ra_renewals r
    -- 筛选上月完成的GRM续签
    WHERE r.completion_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
                              AND LAST_DAY(NOW() - INTERVAL 1 MONTH)
    AND r.renewal_type = 'GRM' -- 假设这个字段标识GRM类型的续签
) AS renewal_day_calculations;

关键注意点 #

1. 如果你的completion_date包含时间（比如2024-02-28 15:30:00），记得用DATE(r.completion_date)截断时间，避免漏选或多选
2. 节假日表一定要只存非周末的节假日，不然会重复扣除天数
3. 如果你的数据库有内置的工作日函数（比如SQL Server的NETWORKDAYS、Oracle的BUSINESS_DAYS），可以直接替换掉子查询里的手动计算部分，会更简洁
4. 如果xx和yy的顺序可能颠倒（比如end_date早于start_date），记得加个ABS()或者判断，避免算出负数

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