按账龄拆分发票金额至对应列并生成列总计的SQL实现需求

直接给出可运行的SQL方案，替代Excel手动处理流程：

SELECT
    CASE WHEN GROUPING(InvoiceNumber) = 1 THEN 'Total' 
         ELSE CAST(RTS_ARByInvoiceCustomerInfo.InvoiceNumber AS VARCHAR(20)) 
    END AS 'Invoice#',
    SUM(CASE WHEN DaysFromDueDate < 30 THEN AmountRemaining ELSE NULL END) AS 'current (less than 30)',
    SUM(CASE WHEN DaysFromDueDate BETWEEN 31 AND 60 THEN AmountRemaining ELSE NULL END) AS '31-60 days',
    SUM(CASE WHEN DaysFromDueDate BETWEEN 61 AND 90 THEN AmountRemaining ELSE NULL END) AS '61-90 days',
    CASE WHEN GROUPING(InvoiceNumber) = 1 THEN 'sum'
         ELSE SUM(CASE WHEN DaysFromDueDate > 90 THEN AmountRemaining ELSE NULL END)
    END AS '91+',
    SUM(AmountRemaining) AS 'Total'
FROM
    TrulinXLive.dbo.RTS_ARByInvoiceCustomerInfo RTS_ARByInvoiceCustomerInfo
GROUP BY
    ROLLUP(RTS_ARByInvoiceCustomerInfo.InvoiceNumber)
ORDER BY
    CASE WHEN GROUPING(InvoiceNumber) = 1 THEN 1 ELSE 0 END,
    RTS_ARByInvoiceCustomerInfo.InvoiceNumber

关键逻辑说明 #

• 账龄列分配：通过CASE语句判断DaysFromDueDate的区间，将AmountRemaining映射到对应列，不符合区间的返回NULL，对应查询结果显示空白。
• 总计行生成：使用ROLLUP(InvoiceNumber)分组自动生成汇总行；GROUPING(InvoiceNumber)=1识别总计行，将该行的Invoice#设为'Total'，91+列设为'sum'。
• 排序控制：ORDER BY中的条件确保总计行排在所有发票数据之后，同时保持发票编号的升序排列。
• 数值汇总：各账龄列用SUM()聚合，单发票行显示自身金额，总计行显示对应区间的总和；Total列直接汇总所有剩余金额，保证与各账龄列总和一致。

将示例数据代入该查询，会生成与预期完全一致的账单结果，无需再依赖Excel的手动公式处理。

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