Creditor - Debit + previous row Balance

Question

I have a table with four columns ( id , Creditor ,Debit ,Balance) I want to work

-----------------------------
| id |Creditor|Debit|Balance|
----------------------------
| 1  | 100    |null | null  |
| 2  | 50     |null | null  |
| 3  | null   | 100 | null  |
| 4  | 50     |null | null  |
| 5  | null   | 20  | null  |
| 6  | null   | 10  | null  |
| 7  | null   | 100 | null  |
| 8  | 200    |null | null  |
-----------------------------

How can I achieve this

Expected Output

 -----------------------------
| id |Creditor|Debit|Balance|
----------------------------
| 1  | 100    |null | 100   |
| 2  | 50     |null | 150   |
| 3  | null   | 100 | 50    |
| 4  | 50     |null | 100   |
| 5  | null   | 20  | 80    |
| 6  | null   | 10  | 70    |
| 7  | null   | 100 | -30   |
| 8  | 200    |null | 170   |
-----------------------------

The downvote is because you showed no effort. Not from me though. — Tim Biegeleisen, May 08 '18 at 02:17

score 0 · Accepted Answer · answered May 08 '18 at 02:19

You can use a cumulative sum:

select (coalesce(sum(creditor) over (order by id), 0) +
        coalesce(sum(debitor) over (order by id), 0)
       ) as balance

To set the value, you can use an updatable CTE:

with toupdate as (
      select t.*
             (coalesce(sum(creditor) over (order by id), 0) +
              coalesce(sum(debitor) over (order by id), 0)
             ) as new_balance
      from t
     )
update toupdate
    set balance = new_balance;

Creditor - Debit + previous row Balance

1 Answers1