SQL实现无成本月份合同金额滚动累计RunningTotal值填充

现有逻辑存在两处核心问题：

• 合同分摊额ContractPerMonth依赖成本表JCCP关联计算，无成本发生的月份不会出现在中间查询结果中，后续循环补空行时直接硬编码写入0，没有匹配合同分摊规则
• 滚动累计值RunningTotal在插入成本数据的步骤通过窗口函数计算，仅覆盖有成本记录的月份，补入的空月份没有重新计算全周期累计值，也没有匹配首尾月份ContractPerMonth显示为0、累计值逐月累加的规则。

• 提前单独计算项目起止日期、总周期月数、单月合同分摊额，不依赖成本表取值，避免无成本月份拿不到合同参数
• 用递归CTE生成项目全周期连续月份表，替代原有先插成本数据再循环补空行的逻辑，从根源上保证月份连续无缺失
• 拆分合同额计算逻辑：累计计算用的基数每月固定为单月分摊额，保证滚动累加连续；ContractPerMonth显示字段按规则赋值，首月、末月为0，其余周期月份为单月分摊额
• 连续月份表左关联聚合后的成本数据，无成本月份的三类成本自动补0，最后基于全量连续月份计算滚动累计值。

Create Table #costs(fiscalMonth date, Labor numeric(12,2), Equipment numeric(12,2), Indirect numeric(12,2), RunningTotal numeric(12,2), ContractPerMonth numeric(12,2))

Declare @startDate as date, @endDate as date, @totalMonths int, @contractPerMonthVal numeric(12,2)

-- 一次性读取项目基础参数
select 
    @startDate = StartMonth, 
    @endDate = ProjCloseDate,
    @totalMonths = DATEDIFF(Month, StartMonth, ProjCloseDate) + 1,
    @contractPerMonthVal = CAST(CASE WHEN SUM(ContractAmt) = 0 THEN 0 
                                ELSE SUM(ContractAmt) / (DATEDIFF(Month, StartMonth, ProjCloseDate) + 1) 
                            END as numeric(12,2))
from JCCM
WHERE ltrim(rtrim(Contract)) = (@Job)

;WITH AllMonths AS (
    -- 生成项目全周期连续月份，区分累计计算基数和显示用的ContractPerMonth
    SELECT 
        @startDate AS Mth,
        @contractPerMonthVal AS RunningBase,
        CASE WHEN @startDate IN (@startDate, @endDate) THEN 0 ELSE @contractPerMonthVal END AS ContractAmtPerMonth
    UNION ALL
    SELECT 
        DATEADD(month, 1, Mth),
        @contractPerMonthVal,
        CASE WHEN DATEADD(month, 1, Mth) IN (@startDate, @endDate) THEN 0 ELSE @contractPerMonthVal END
    FROM AllMonths
    WHERE Mth < @endDate
),
CostAgg AS (
    -- 聚合所有月份的三类成本，用行内CASE替代多层分组减少嵌套
    SELECT
        cp.Mth
        ,ISNULL(SUM(CASE WHEN ct.JBCostTypeCategory = 'L' THEN cp.ActualCost ELSE 0 END),0) as LaborCost
        ,ISNULL(SUM(CASE WHEN ct.JBCostTypeCategory = 'E' THEN cp.ActualCost ELSE 0 END),0) as EquipmentCost
        ,ISNULL(SUM(CASE WHEN ct.JBCostTypeCategory = 'O' THEN cp.ActualCost ELSE 0 END),0) as IndirectCost
    FROM JCCP cp
    LEFT JOIN JCCT ct ON cp.PhaseGroup = ct.PhaseGroup AND cp.CostType = ct.CostType
    LEFT JOIN JCJM jm ON cp.JCCo = jm.JCCo and cp.Job = jm.Job
    WHERE
        cp.JCCo IN (@Company)
        AND ltrim(rtrim(cp.Job)) = (@Job)
        AND ct.JBCostTypeCategory IN ('L','E','O')
    GROUP BY cp.Mth
)
-- 关联全月份和成本数据，计算滚动累计后插入临时表
INSERT INTO #costs
SELECT
    am.Mth AS fiscalMonth,
    ISNULL(ca.LaborCost,0) AS Labor,
    ISNULL(ca.EquipmentCost,0) AS Equipment,
    ISNULL(ca.IndirectCost,0) AS Indirect,
    SUM(am.RunningBase) OVER (ORDER BY am.Mth) AS RunningTotal,
    am.ContractAmtPerMonth AS ContractPerMonth
FROM AllMonths am
LEFT JOIN CostAgg ca ON am.Mth = ca.Mth
OPTION (MAXRECURSION 1000) -- 支持最长83年的项目周期，覆盖所有常规业务场景

-- 输出排序后的结果
SELECT * FROM #costs ORDER BY fiscalMonth
Drop table #costs

• 修正后无成本月份的三类成本自动补0，RunningTotal按规则逐月累加，不会出现0值断裂
• 完全匹配预期结果规则：首尾月份ContractPerMonth显示为0，中间执行周期月份显示单月分摊额
• 去掉了原有冗余的循环判断、计数逻辑，执行效率更高。

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