CODEWITHSUNDEEP
sqlsql-server
Chris Hauer
Chris Hauer

Reputation: 1

Distribute invoice amounts into columns based on a separate value

Setup: I'm trying to make a statement, where invoice amounts are dropped into columns based on their age. Id also like each column to have a total at the bottom.

Right now, I dump the data into Excel and manipulate with index(match) and an if statement. It's ugly and open to human error.

I'm trying to look at the DaysDue field of my raw data, and distribute the InvBlance into the appropriate columns as below. sample data:

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 

Invoice# | current (less than 30 | 31-60 days | 61-90 days | 91+    |     Total
1        |                       |            |   8500.00  |        |   8500.00
2        |                       |    3500.00 |            |        |   3500.00
3        |        1500.00        |            |            |        |   1500.00
4        |        1000.00        |            |            |        |   1000.00
Total    |        2500.00        |    3500.00 |   8500.00  | sum    |  14500.00

This is my code so far. Also this is a live database.

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

Thanks for any help.

Upvotes: 0

Views: 53

Answers (1)

Bjorg P
Bjorg P

Reputation: 1073

Here is a solution. I created a table variable with your sample data:

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];

The output is:

Invoice # Current (less than 30) 31-60 days 61-90 days 91+ Total
1 NULL NULL 8500.00 NULL 8500.00
2 NULL 3500.00 NULL NULL 3500.00
3 1500.00 NULL NULL NULL 1500.00
4 1000.00 NULL NULL NULL 1000.00
Total 2500.00 3500.00 8500.00 NULL 14500.00

You can adjust for your actual table name, etc.

Upvotes: 1

Related Questions