I'm having a problem allocating payments to invoice lines. We’re using MsSql 2014
Data looks like this:
Invoice lines table (sales):
lineId invoiceId value
1 1 100
2 1 -50
3 1 40
4 2 500
Payments table (payments):
paymentId invoiceId amount
1 1 50
2 1 40
3 2 300
Now, I want to know for each invoice line the payment details. The payments shall be allocated first to the smallest values (i.e. line 2, -50)
The output should look like this:
lineId invoiceId value paymentId valuePaid valueUnpaid
2 1 -50 1 -50 0
3 1 40 1 40 0
1 1 100 1 60 40
1 1 100 2 40 0
4 2 500 3 300 200
The problem is solved in the post below, but the solution does not work if you have negative invoice values or if you have to split an invoice line in two payments.
How can I use running total aggregates in a query to output financial accumulations?
This is what I've done so far based on the article above:
drop table dbo.#sales
drop table dbo.#payments
CREATE TABLE dbo.#sales
( lineId int primary key, -- unique line id
invoiceId int not null , -- InvoiceId foreign key
itemValue money not null ) -- value of invoice line.
CREATE TABLE dbo.#payments
( paymentId int primary key, -- Unique payment id
InvoiceId int not null, -- InvoiceId foreign key
PayAmount money not null
)
-- Example invoice, id #1, with 3 lines, total ammount = 90; id #2, with one line, value 500
INSERT dbo.#sales VALUES
(1, 1, 100),
(2, 1, -50),
(3, 1, 40),
(4, 2, 500) ;
-- Two payments paid towards invoice id#1, 50+40 = 90
-- One payment paid towards invoice id#2, 300
INSERT dbo.#Payments
VALUES (1, 1, 50),
(2, 1, 40),
(3, 2, 300);
-- Expected output should be as follows, for reporting purposes.
/* lineId, invoiceId, value, paymentId, valuePaid, valueUnpaid
2, 1, -50, 1, -50, 0
3, 1, 40, 1, 40, 0
1, 1, 100, 1, 60, 40
1, 1, 100, 2, 40, 0
4, 2, 500, 3, 300, 200 */
WITH inv AS
( SELECT lineId, invoiceId,
itemValue,
SumItemValue = SUM(itemValue) OVER
(PARTITION BY InvoiceId
ORDER BY ItemValue Asc
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM dbo.#Sales
)
, pay AS
( SELECT
PaymentId, InvoiceId, PayAmount as PayAmt,
SumPayAmt = SUM(PayAmount) OVER
(PARTITION BY InvoiceId
ORDER BY PaymentId
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM dbo.#payments
)
SELECT
inv.lineId,
inv.InvoiceId,
inv.itemValue,
pay.PaymentId,
PaymentAllocated =
CASE WHEN SumPayAmt <= SumItemValue - itemValue
OR SumItemValue <= SumPayAmt - PayAmt
THEN 0
ELSE
CASE WHEN SumPayAmt <= SumItemValue THEN SumPayAmt
ELSE SumItemValue END
- CASE WHEN SumPayAmt-PayAmt <= SumItemValue-itemValue
THEN SumItemValue-itemValue
ELSE SumPayAmt-PayAmt END
END
FROM inv JOIN pay
ON inv.InvoiceId = pay.InvoiceId
ORDER BY
inv.InvoiceId,
pay.PaymentId;
The current output is:
lineId InvoiceId itemValue PaymentId PaymentAllocated
2 1 -50.00 1 0.00
3 1 40.00 1 0.00
1 1 100.00 1 50.00
2 1 -50.00 2 0.00
3 1 40.00 2 0.00
1 1 100.00 2 40.00
4 2 500.00 3 300.00
Any direction will be appreciated. Thank you.
More info on the allocation rules:
- Allocating first payment to the smallest sale (i.e. -50) was just a convention to insure all sales lines get payments. If I’d allocate arbitrary or with another rule, and line 1 (value 100) would get the first payment, I’d use all the payment for this line and the rest of the invoice would remain unallocated.
- As I said, it’s just an convention. If someone else comes with a different rule that works, it’s ok. Actually, the structure is simplified compared with the production tables: payments also have a payment date, type, … and a correct distribution should tell us what invoice lines were paid at each payment time.
- Payments are restricted by the logic of the system to be smaller then the sum of the invoice lines. Well, it might be a case when payments are greater: the total invoice is negative (ie: -100). In this case we can insert in the payments table amounts in the range of -100: 0 and Total Payments are restricted to -100