Ismail Sensei Ismail Sensei - 6 days ago 6
SQL Question

Recursive subtraction of two columns?

Hi is wrote an SQL to give me the rest of what left in invoice payment
REST = (Invoice Total - Payment)

What i have:

Invoice_ID|Invoice Total | Payment | REST
----------|----------------|-----------|---------
01 |93 | 10 | 69
01 |93 | 9 | 69
01 |93 | 5 | 69


This what i got, it's correct but what i really want is recursive REST

Invoice_ID|Invoice Total | Payment | REST
----------|----------------|-----------|---------
01 |93 | 10 | 83
01 |93 | 9 | 74
01 |93 | 5 | 69

Answer

Try like below. Do running total on Payment column and subtract that value with your invoice value.

SELECT Invoice_ID,InvoiceTotal   ,Payment,
       InvoiceTotal- Sum(payment)
                   OVER (
                     partition BY Invoice_ID
                     ORDER BY rno) rest
FROM   (SELECT *,
               Row_number()
                 OVER (
                   partition BY Invoice_ID
                   ORDER BY Invoice_ID) rno
        FROM   table_name)a 
Comments