MS Access/VBA如何按月份拆分日期范围行并分摊净价

针对你的需求，这里提供两种基于递归CTE的SQL实现方案，分别对应按天数比例分摊和按月份数量均分两种净价计算逻辑，可根据实际场景选择。

方案一：按实际天数比例分摊净价 #

适用于需要精准按每个拆分区间的实际天数占总天数比例计算净价的场景：

WITH RECURSIVE date_splits AS (
    -- 锚点：生成跨月记录的第一个日期区间
    SELECT
        MesspNr,
        RechnNr,
        AbDat AS current_start,
        LEAST(LAST_DAY(AbDat), BisDat) AS current_end,
        BisDat AS original_end,
        NettBetr AS original_amount,
        DATEDIFF(BisDat, AbDat) + 1 AS total_days
    FROM your_table
    WHERE DATEDIFF(BisDat, AbDat) + 1 > DAY(LAST_DAY(AbDat))
    UNION ALL
    -- 递归：生成后续月份的日期区间，直到覆盖原结束日期
    SELECT
        MesspNr,
        RechnNr,
        DATE_ADD(LAST_DAY(current_start), INTERVAL 1 DAY) AS current_start,
        LEAST(LAST_DAY(DATE_ADD(LAST_DAY(current_start), INTERVAL 1 DAY)), original_end) AS current_end,
        original_end,
        original_amount,
        total_days
    FROM date_splits
    WHERE current_end < original_end
)
-- 合并原表与拆分结果，计算分摊后的净价
SELECT
    t.MesspNr,
    t.RechnNr,
    COALESCE(ds.current_start, t.AbDat) AS AbDat,
    COALESCE(ds.current_end, t.BisDat) AS BisDat,
    ROUND(
        COALESCE(
            ds.original_amount * (DATEDIFF(ds.current_end, ds.current_start) + 1) / ds.total_days,
            t.NettBetr
        ),
        2
    ) AS NettBetr
FROM your_table t
LEFT JOIN date_splits ds
    ON t.MesspNr = ds.MesspNr AND t.RechnNr = ds.RechnNr
WHERE ds.current_start IS NOT NULL OR (DATEDIFF(t.BisDat, t.AbDat) + 1 <= DAY(LAST_DAY(t.AbDat)))
ORDER BY t.MesspNr, t.RechnNr, AbDat;

代码说明 #

1. 递归CTE date_splits 负责拆分跨月记录：
   • 锚点查询筛选出跨月的记录，生成第一个区间（原起始日到当月最后一天，不超过原结束日），同时记录总天数和原净价。
   • 递归查询循环生成后续每个月的区间，直到当前区间的结束日期等于原结束日期。
2. 最终查询通过左连接合并原表与拆分结果，区分跨月/非跨月记录，按天数比例计算分摊后的净价，保留两位小数（可按需调整精度）。

方案二：按月份数量均分净价 #

适用于无需精准天数计算、直接按拆分后的月份数量均分净价的场景：

WITH RECURSIVE date_splits AS (
    -- 锚点：生成跨月记录的第一个日期区间，计算总月份数
    SELECT
        MesspNr,
        RechnNr,
        AbDat AS current_start,
        LEAST(LAST_DAY(AbDat), BisDat) AS current_end,
        BisDat AS original_end,
        NettBetr AS original_amount,
        PERIOD_DIFF(DATE_FORMAT(BisDat, '%Y%m'), DATE_FORMAT(AbDat, '%Y%m')) + 1 AS total_months
    FROM your_table
    WHERE PERIOD_DIFF(DATE_FORMAT(BisDat, '%Y%m'), DATE_FORMAT(AbDat, '%Y%m')) > 0
    UNION ALL
    -- 递归：生成后续月份的日期区间
    SELECT
        MesspNr,
        RechnNr,
        DATE_ADD(LAST_DAY(current_start), INTERVAL 1 DAY) AS current_start,
        LEAST(LAST_DAY(DATE_ADD(LAST_DAY(current_start), INTERVAL 1 DAY)), original_end) AS current_end,
        original_end,
        original_amount,
        total_months
    FROM date_splits
    WHERE current_end < original_end
)
-- 合并原表与拆分结果，计算均分后的净价
SELECT
    t.MesspNr,
    t.RechnNr,
    COALESCE(ds.current_start, t.AbDat) AS AbDat,
    COALESCE(ds.current_end, t.BisDat) AS BisDat,
    ROUND(
        COALESCE(
            ds.original_amount / ds.total_months,
            t.NettBetr
        ),
        2
    ) AS NettBetr
FROM your_table t
LEFT JOIN date_splits ds
    ON t.MesspNr = ds.MesspNr AND t.RechnNr = ds.RechnNr
WHERE ds.current_start IS NOT NULL OR PERIOD_DIFF(DATE_FORMAT(t.BisDat, '%Y%m'), DATE_FORMAT(t.AbDat, '%Y%m')) = 0
ORDER BY t.MesspNr, t.RechnNr, AbDat;

代码说明 #

1. 递归CTE date_splits 新增计算总月份数（通过PERIOD_DIFF函数计算起止日期的月份差，再加1得到总月份数）。
2. 最终查询直接将原净价除以总月份数，得到每个拆分行的净价，逻辑更简洁。

内容的提问来源于stack exchange，提问作者Mr. Anderson