# Determining the overlap between payments and invoices

Given a table containing a list of invoices and a table containing a list of payments, I have the following problem:

• Payments should be allocated to invoices up to the total amount of the invoice.
• If the size of the payment is greater than the remaining amount on the invoice, the remainder should be allocated to the next invoice in line.
• If there are no more invoices in line, the total amount of the invoice can be exceeded. All remaining payments can be allocated to the final invoice.

For example, given the following tables:

InvoicedId InvoiceAmount
1 5.00
2 5.00
3 5.00
PaymentId PaymentAmount
1 4.00
2 4.00
3 4.00
4 4.00

The desired result would be the following:

InvoiceId InvoiceAmount PaymentId PaymentAmount
1 5.00 1 4.00
1 5.00 2 1.00
2 5.00 2 3.00
2 5.00 3 2.00
3 5.00 3 2.00
3 5.00 4 4.00

The best solution I was able to come up with involves loops and/or cursors, but my gut tells me there's a much better way to do it that I'm just not seeing.

Here is one approach (Fiddle).

You didn't specify any particular version so I used SQL Server 2022 syntax. If you are on a previous version analogues of all the functionality used are possible if cumbersome.

It emits a "before" and "after" row for all Payments (P) and all Invoices (I) and combines them in order of cumulative total.

It then extracts all the "after" rows ("1" suffix) - which represents the invoice being fully paid or a payment being exhausted and uses `LAST_VALUE` to find the InvoiceId/PaymentId that were applicable at that point.

e.g. For following input data

``````  DECLARE @Invoice TABLE(InvoicedId INT, InvoiceAmount DECIMAL(10,2));

INSERT @Invoice
VALUES (201,5), /*Entirely met by Payment 101*/
(202,5), /*Entirely met by Payment 101*/
(203,100), /*met by balance of Payment 101 and entirety of 102, 103*/
(204,4),  /*Entirely met by Payment 104*/
(205,4)   /*Entirely met by Payment 105*/

DECLARE @Payment TABLE(PaymentId INT, PaymentAmount DECIMAL(10,2));

INSERT @Payment
VALUES (101,12),
(102,50),
(103,48),
(104,4),
(105,4),
(106,4); /*Surplus payment, allocate to last invoice 205 */
``````

It gets it into the following form

To return the results

InvoiceId InvoiceAmount PaymentId PaymentAmount
201 5.00 101 5.00
202 5.00 101 5.00
203 100.00 101 2.00
203 100.00 102 50.00
203 100.00 103 48.00
204 4.00 104 4.00
205 4.00 105 4.00
205 4.00 106 4.00
``````WITH I
AS (SELECT InvoicedId,
InvoiceAmount,
ISNULL(SUM(InvoiceAmount) OVER (ORDER BY InvoicedId ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS PrevCumeTotal,
SUM(InvoiceAmount) OVER (ORDER BY InvoicedId ROWS UNBOUNDED PRECEDING) AS CumeTotal
FROM   @Invoice),
P
AS (SELECT PaymentId,
PaymentAmount,
ISNULL(SUM(PaymentAmount) OVER (ORDER BY PaymentId ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS PrevCumeTotal,
SUM(PaymentAmount) OVER (ORDER BY PaymentId ROWS UNBOUNDED PRECEDING) AS CumeTotal
FROM   @Payment),
Unioned
AS (SELECT CA.Type,
CA.CumeTotal,
Id = InvoicedId,
Amount = InvoiceAmount
FROM   I
CROSS APPLY (SELECT '0I', PrevCumeTotal
UNION ALL
SELECT '1I', CumeTotal) CA(Type, CumeTotal)
UNION ALL
SELECT CA.Type,
CA.CumeTotal,
PaymentId,
PaymentAmount
FROM   P
CROSS APPLY (SELECT '0P', PrevCumeTotal
UNION ALL
SELECT '1P', CumeTotal) CA(Type, CumeTotal)
),
Windowed
AS (SELECT *,
PrevCumeTotal = LAST_VALUE(CASE WHEN Type IN (Type) THEN CumeTotal END) ignore nulls OVER win,
LastOpenedPaymentId = LAST_VALUE(CASE WHEN Type = '0P' THEN Id END) ignore nulls OVER win,
LastClosedPaymentId = LAST_VALUE(CASE WHEN Type = '1P' THEN Id END) ignore nulls OVER win,
InvoiceAmount = LAST_VALUE(CASE WHEN Type = '0I' THEN Amount END) ignore nulls OVER win,
InvoiceId = LAST_VALUE(CASE WHEN Type = '0I' THEN Id END) ignore nulls OVER win
FROM   Unioned
WINDOW win AS (ORDER BY CumeTotal, Type DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
)
SELECT InvoiceId,
InvoiceAmount,
PaymentId = CASE WHEN LastOpenedPaymentId IS DISTINCT FROM LastClosedPaymentId THEN LastOpenedPaymentId END,
PaymentAmount = SUM(CASE WHEN LastOpenedPaymentId IS DISTINCT FROM LastClosedPaymentId THEN CumeTotal - PrevCumeTotal END)
FROM Windowed
WHERE Type IN ('1P', '1I') AND LastOpenedPaymentId IS DISTINCT FROM LastClosedPaymentId
GROUP BY InvoiceId,
InvoiceAmount,
LastOpenedPaymentId,
LastClosedPaymentId
``````
• Sorry, I should have mentioned I was on 2019, but the adjustments were pretty straight forward. Thank you very much, this is a really nice solution! Jun 21, 2023 at 21:45
• @warhorus - glad you got it working - the main thing I envisaged being more cumbersome was simulating the `ignore nulls` for `LAST_VALUE` - e.g. with something like this itprotoday.com/sql-server/last-non-null-puzzle Jun 21, 2023 at 21:51
• Yeah, that was definitely the most annoying part. I added a `ROW_NUMBER` column onto `Windowed` and then added a new CTE, `WindowedIgnoringNulls` that backfilled any nulls in those columns with something to the effect of `SELECT TOP 1 WHERE IS NOT NULL AND ROW_NUMBER < CURRENT_ROW_NUMBER`. Maybe not the most efficient way to do it, but it worked for what I needed. Jun 22, 2023 at 22:11