SQL Server中将月度成本分摊至每日记录的技术问询

我来帮你解决这个月度成本分摊的需求！咱们先明确目标：把表中的月度费用（比如办公室租金）拆分成对应周期内每一天的分摊记录，同时保留原本的每日费用记录不变。

原表数据 #

首先看一下你的原始表查询和数据：

SELECT [ExpensesID], [ExpDate], [ExpsType], [ExpDetails], [ExpAmount], [USDRate], [ExpenseBy] 
FROM [PSationMIS].[dbo].[Expenses]

返回的数据：

期望输出 #

你想要的结果是：保留每日午餐的原始记录，同时生成30条租金的每日分摊记录（从2018-05-16到2018-06-15），每条记录的ExpAmount约为166.66。

问题分析 #

你之前的查询用了GROUP BY，但这是聚合操作，没办法生成多行的每日记录。咱们需要先生成目标周期内的所有日期，再把月度费用和这些日期关联，计算每日分摊金额。

解决方案 #

这里给你两种实用的方案，任选其一即可：

方案一：递归CTE生成日期序列（通用型）

这个方法不依赖系统表，适合所有SQL Server版本：

WITH DateSeries AS (
    -- 初始化：取出月度费用的起始日期和周期结束日期（ExpDate +1个月 -1天）
    SELECT 
        ExpensesID,
        ExpDate AS CurrentDate,
        DATEADD(DAY, -1, DATEADD(MONTH, 1, ExpDate)) AS EndDate,
        ExpAmount,
        ExpsType,
        ExpDetails,
        USDRate,
        ExpenseBy
    FROM [PSationMIS].[dbo].[Expenses]
    WHERE ExpsType LIKE '%Monthly%' -- 筛选月度费用
    
    UNION ALL
    
    -- 递归生成后续每一天的日期
    SELECT 
        ExpensesID,
        DATEADD(DAY, 1, CurrentDate) AS CurrentDate,
        EndDate,
        ExpAmount,
        ExpsType,
        ExpDetails,
        USDRate,
        ExpenseBy
    FROM DateSeries
    WHERE CurrentDate < EndDate
)
-- 合并月度分摊记录和原始每日费用记录
SELECT 
    ExpensesID,
    CurrentDate AS ExpDate,
    ExpsType,
    ExpDetails,
    ROUND(ExpAmount / DATEDIFF(DAY, ExpDate, DATEADD(MONTH, 1, ExpDate)), 2) AS ExpAmount,
    USDRate,
    ExpenseBy
FROM DateSeries
UNION ALL
-- 保留原始的每日费用记录
SELECT 
    ExpensesID,
    ExpDate,
    ExpsType,
    ExpDetails,
    ExpAmount,
    USDRate,
    ExpenseBy
FROM [PSationMIS].[dbo].[Expenses]
WHERE ExpsType LIKE '%Daily%' -- 筛选每日费用
ORDER BY ExpDate, ExpensesID;

方案二：用系统表生成日期序列（高性能型）

如果你的SQL Server环境允许访问master..spt_values，这个方法性能更优：

-- 生成日期序列并关联月度费用，计算每日分摊
SELECT 
    e.ExpensesID,
    DATEADD(DAY, n.number, e.ExpDate) AS ExpDate,
    e.ExpsType,
    e.ExpDetails,
    ROUND(e.ExpAmount / DATEDIFF(DAY, e.ExpDate, DATEADD(MONTH, 1, e.ExpDate)), 2) AS ExpAmount,
    e.USDRate,
    e.ExpenseBy
FROM [PSationMIS].[dbo].[Expenses] e
JOIN master..spt_values n 
    ON n.type = 'P' 
    AND n.number < DATEDIFF(DAY, e.ExpDate, DATEADD(MONTH, 1, e.ExpDate))
WHERE e.ExpsType LIKE '%Monthly%'

UNION ALL

-- 追加原始的每日费用记录
SELECT 
    ExpensesID,
    ExpDate,
    ExpsType,
    ExpDetails,
    ExpAmount,
    USDRate,
    ExpenseBy
FROM [PSationMIS].[dbo].[Expenses]
WHERE ExpsType LIKE '%Daily%'

ORDER BY ExpDate, ExpensesID;

关键说明 #

• 两种方案都会先处理月度费用，生成周期内的每一天记录，再用UNION ALL合并原始的每日费用记录。
• ROUND(ExpAmount / 分摊天数, 2)用来把分摊金额四舍五入到两位小数，和你期望的166.66一致。
• 递归CTE的方式更灵活，适合所有版本；系统表的方式如果可用，执行速度更快。

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