根据单独的值将发票金额分配到列中

Question

设置：我想做一个报表，根据发票金额的年龄将其分为几列。我也希望每一列的底部都有一个总数。

现在，我将数据转储到Excel中，并使用index(match)和if语句进行操作。这是丑陋的，容易被人为错误所伤害。

我试图查看我的原始数据的DaysDue字段，并将InvBlanc分布到适当的列中，如下所示。样本数据：

invoice: 1 daysdue:85 Invbalance: 8500.00  
invoice: 2 daysdue:35 Invbalance: 3500.00  
invoice: 3 daysdue:15 Invbalance: 1500.00  
invoice: 4 daysdue:10 Invbalance: 1000.00 

发票#|当前(少于30 |31-60天|61-90天|91+|总计1|||8500.00 ||85000.002||350.00|||350.003 | 1500.00 | | | 1500.004|1000.00 |||1000.00总计|2500.00 |3500.00 |8500.00 |sum|14500.00

这是我迄今为止的代码。这也是一个实时数据库。

SELECT  
RTS_ARByInvoiceCustomerInfo.InvoiceNumber AS 'Invoice#',  
RTS_ARByInvoiceCustomerInfo.DaysFromDueDate AS 'DaysDue',  
RTS_ARByInvoiceCustomerInfo.AmountRemaining AS 'InvBalance' 
FROM
TrulinXLive.dbo.RTS_ARByInvoiceCustomerInfo RTS_ARByInvoiceCustomerInfo 
ORDER BY 
RTS_ARByInvoiceCustomerInfo.InvoiceNumber

谢谢你的帮助。

Answer 1

这里有一个解决方案。我用你的样本数据创建了一个表变量：

DECLARE @Data TABLE
(
[InvoiceID] INT            NOT NULL,
[DaysDue]   INT            NOT NULL,
[Balance]   DECIMAL(10, 2) NOT NULL
);
INSERT INTO @Data
(
[InvoiceID],
[DaysDue],
[Balance]
)
VALUES
(1, 85, 8500.00),
(2, 35, 3500.00),
(3, 15, 1500.00),
(4, 10, 1000.00);
;WITH [transformed]
AS (SELECT CAST([InvoiceID] AS VARCHAR(10)) AS [Invoice #],
CASE WHEN [DaysDue] BETWEEN 0 AND 29 THEN
[Balance]
ELSE
NULL
END AS [Current (less than 30)],
CASE WHEN [DaysDue] BETWEEN 30 AND 60 THEN
[Balance]
ELSE
NULL
END AS [31-60 days],
CASE WHEN [DaysDue] BETWEEN 61 AND 90 THEN
[Balance]
ELSE
NULL
END AS [61-90 days],
CASE WHEN [DaysDue] > 90 THEN
[Balance]
ELSE
NULL
END AS [91+],
[Balance] AS [Total]
FROM   @Data)
SELECT [transformed].[Invoice #],
[transformed].[Current (less than 30)],
[transformed].[31-60 days],
[transformed].[61-90 days],
[transformed].[91+],
[transformed].[Total]
FROM   [transformed]
UNION ALL
SELECT 'Total',
SUM([transformed].[Current (less than 30)]),
SUM([transformed].[31-60 days]),
SUM([transformed].[61-90 days]),
SUM([transformed].[91+]),
SUM([transformed].[Total])
FROM   [transformed];

输出为：

>总计>空空14500.00

发票号		当前(小于30)	31-60天	161-90天	91+
1	NULL	8500.00	NULL	85000.00
2	NULL	3500.00	空	空	3500.00
350.00	空
4	1000.00	空
总计	2500.00	3500.00	8500.00	空