SQL如何将跨月起止日期的间隔天数拆分分摊到各自然月

问题说明 #

现有数据库存储车辆维保记录的两个核心时间字段：

• StartDate：维保停留开始时间
• EndDate：维保停留结束时间
  直接使用DATEDIFF计算两日期时间差时，跨多个月份的时长会被全部归属到起始月或结束月，无法支撑按自然月统计每月维保停留天数的分析需求。

典型场景示例

• StartDate: '2022-04-28 06:33:34.000'
• EndDate: '2022-06-20 14:09:45.000'
• 总时长为53天7小时36分11秒，按业务规则向上取整为54天，需拆分到对应自然月：4月计3天、5月计31天、6月计20天。
  当前仅能计算总时长，无法完成月度拆分的逻辑如下：

-- 计算精确到小数的总天数，示例场景返回53.317
CAST(CAST(DATEDIFF(s, STARTDATE, ENDDATE)AS float)/86400 AS DECIMAL(16,3))  AS CAR_TOTAL_DAYS_PERC

-- 计算取整的总天数，示例场景返回53
DATEDIFF(s, STARTDATE, ENDDATE) / 86400  AS CAR_TOTAL_DAYS

可直接落地的SQL实现（兼容SQL Server） #

核心实现思路：

• 递归生成每条维保记录覆盖的所有自然月序列
• 逐次截取每个月内实际发生维保停留的时间区间
• 按规则计算每个区间的停留天数，完成分摊

WITH month_series AS (
    -- 锚点：获取每条记录起始时间所在月的月初
    SELECT
        CAR_ID,
        STARTDATE,
        ENDDATE,
        DATEFROMPARTS(YEAR(STARTDATE), MONTH(STARTDATE), 1) AS current_month_start
    FROM vehicle_maintenance
    UNION ALL
    -- 递归生成后续覆盖的月份，直到结束时间所在月为止
    SELECT
        CAR_ID,
        STARTDATE,
        ENDDATE,
        DATEADD(MONTH, 1, current_month_start) AS current_month_start
    FROM month_series
    WHERE DATEADD(MONTH, 1, current_month_start) <= DATEFROMPARTS(YEAR(ENDDATE), MONTH(ENDDATE), 1)
)
SELECT
    CAR_ID,
    STARTDATE,
    ENDDATE,
    current_month_start AS stat_month,
    -- 取当月月初与维保开始时间的较晚值，作为当月停留区间起点
    CASE WHEN STARTDATE > current_month_start THEN STARTDATE ELSE current_month_start END AS range_start,
    -- 取当月月末与维保结束时间的较早值，作为当月停留区间终点
    CASE WHEN ENDDATE < EOMONTH(current_month_start) THEN ENDDATE ELSE EOMONTH(current_month_start) END AS range_end,
    -- 按秒计算区间时长后转天，向上取整匹配业务拆分规则
    CEILING(
        DATEDIFF(
            s,
            CASE WHEN STARTDATE > current_month_start THEN STARTDATE ELSE current_month_start END,
            CASE WHEN ENDDATE < EOMONTH(current_month_start) THEN ENDDATE ELSE EOMONTH(current_month_start) END
        ) / 86400.0
    ) AS monthly_stay_days
FROM month_series
-- 配置递归深度，支持最长跨83年的维保记录，覆盖绝大多数业务场景
OPTION (MAXRECURSION 1000);

逻辑验证 #

针对给出的示例场景，上述代码返回结果完全符合拆分要求：

• 2022年4月：停留区间为4月28日06:33:34至4月30日23:59:59，时长约2.73天，向上取整为3天
• 2022年5月：停留区间为5月1日00:00:00至5月31日23:59:59，时长整31天，取整为31天
• 2022年6月：停留区间为6月1日00:00:00至6月20日14:09:45，时长约19.59天，向上取整为20天

适配调整说明 #

• 若不需要向上取整、要保留精确小数天数，直接去掉外层CEILING()函数即可
• 若使用支持GREATEST/LEAST函数的数据库版本，可以替换掉CASE WHEN判断简化代码
• 若使用其他数据库（如MySQL、PG），仅需调整日期生成、日期计算的对应函数即可，核心拆分逻辑不变

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