1

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
Improve this question
5
  • from the expected output it looks (to me) like you're adding the '-50' (credit?) to paymentId=1's value (50 becomes 100), and then you're using 100 to make payments; you might want to explain how (sales) credits are handled in relation to payment values; how do you know which (sales) credits to 'add' to which payment values? why not assign the '-50' credit to paymentId=2? why not pro-rate the '-50' credit across all payments tied to the related invoiceId? also, what would you expect to happen if payments are greater than the total of the related invoice values?
    – markp-fuso
    Oct 13, 2018 at 21:00
  • Thank you @markp for your comment. I edited the post to cover this.
    – Claudiu Radu
    Oct 14, 2018 at 4:56
  • Please give us DDL and DML - or a fiddle (db-fiddle.com/dbfiddle.uk).
    – Vérace
    Oct 14, 2018 at 16:17
  • My guess is that the -50 in lineid 2 is actually an adjustment to the 100 from lineid 1. Unfortunately, I can only guess that based off of ordering of the records - it would be beneficial to know if there is some inherent link. That said, can you treat negative invoice lines as an additional type of credit? I imagine that you would want to keep them separate from payments, but they would behave in the same manner (i.e. they would be allocated to invoice lines to pay them off). You would then perform the statements twice, once to apply the credits, and then a second time to apply the payments.
    – mathewb
    Oct 15, 2018 at 16:27
  • @mathewb Thank you for your comments. Actually, the -50 line might be a product returned, an discount or a credit for advance payments. We also might have the case when the total invoice amount will be negative and will have a negative payment. So, I think is better to leave negative values as invoice lines and not to treat them as payments / credits. We’re using MsSql 2014.
    – Claudiu Radu
    Oct 15, 2018 at 16:48

1 Answer 1

Reset to default
0

In the end I found quite a simple and natural sollution - to allocate payments based on the percentage of each payment in the total value of the invoice. 

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

        SELECT 
            s.lineId,
            s.InvoiceId,
            s.itemValue,
            p.PayAmount,
            p.PaymentId,
            round(p.PayAmount / ts.SumItemValue,3) as PaymentPercent,
            s.ItemValue  * round(p.PayAmount  / ts.SumItemValue,3) as AllocatedPayment
        FROM dbo.#sales s 
        LEFT JOIN dbo.#payments p 
          ON s.InvoiceId = p.InvoiceId
        LEFT JOIN (SELECT invoiceId, sum(itemValue) as SumItemValue FROM dbo.#sales GROUP BY invoiceId) ts 
            ON s.invoiceId = ts.invoiceId
        ORDER BY 
            s.InvoiceId,
            p.PaymentId;

And the resunt looks like this: 

lineId  InvoiceId   itemValue   PayAmount   PaymentId   PaymentPercent  AllocatedPayment
1   1   100.00  50.00   1   0.556   55.60
2   1   -50.00  50.00   1   0.556   -27.80
3   1   40.00   50.00   1   0.556   22.24
3   1   40.00   40.00   2   0.444   17.76
2   1   -50.00  40.00   2   0.444   -22.20
1   1   100.00  40.00   2   0.444   44.40
4   2   500.00  300.00  3   0.60    300.00
Improve this answer

Your Answer

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

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