2

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.

1 Answer 1

2

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

enter image description here

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
3
  • 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!
    – warhorus
    Jun 21, 2023 at 21:45
  • 1
    @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
  • 1
    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.
    – warhorus
    Jun 22, 2023 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.