如何用SQL Server将月度金额按当月天数拆分至每日

需求说明 #

现有按月统计的金额数据，需将其拆分为对应月份的每日数据，月度金额均分到当月每一天（保留两位小数）。例如2022年9月金额20000，每日金额为666.67。

示例源表数据 #

解决方案SQL语句 #

假设源表名为MonthlyData，执行以下查询即可得到每日拆分数据：

WITH MonthDates AS (
    -- 初始化：获取每条月度记录的当月第一天和最后一天
    SELECT 
        Account,
        Project,
        Segment,
        Amount,
        -- 将文本格式的月份转换为当月第一天（若Date列已为日期类型可直接用Date）
        TRY_CONVERT(DATE, '01 ' + Date) AS CurrentDate,
        EOMONTH(TRY_CONVERT(DATE, '01 ' + Date)) AS MonthEnd
    FROM MonthlyData
    UNION ALL
    -- 递归生成当月剩余日期
    SELECT 
        Account,
        Project,
        Segment,
        Amount,
        DATEADD(DAY, 1, CurrentDate),
        MonthEnd
    FROM MonthDates
    WHERE CurrentDate < MonthEnd
)
SELECT 
    Account,
    Project,
    CurrentDate AS Date,
    Segment,
    -- 计算每日金额，保留两位小数
    ROUND(Amount / DAY(MonthEnd), 2) AS Amount
FROM MonthDates
ORDER BY Account, Project, CurrentDate;

关键说明 #

1. 日期转换：若源表的Date列是文本格式（如September 2022），通过拼接'01 '后转换为当月第一天的日期类型；若Date列已存储为日期类型（如2022-09-01），可直接替换为Date字段。
2. 递归生成日期：利用递归CTE从当月第一天开始，逐日生成到当月最后一天的所有日期。
3. 金额均分：通过DAY(MonthEnd)获取当月天数，用月度金额除以天数后用ROUND函数保留两位小数。

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