CODEWITHSUNDEEP
sqlwindow-functions
user16462786
user16462786

Reputation: 115

SQL window function and running total

I'm learning SQL and want to understand the window function better.

Let's say I have a set of data for a bank account containing:

  1. Latest balance (It only shows the latest balance and not historical ones)
  2. Transaction Date
  3. Deposit amount
  4. Withdrawal amount

Out of this data, I want to get a column that shows running total ('balance') at everey transaction as below:

<◎What it should look like>

account latest_balance date deposit withdrawal balance
XYZ 1 000 2021-07-16 100 1 000
XYZ 1 000 2021-07-15 200 900

Because the data does not contain the entire transaction history of this account and the available date is of the latest 1 year, one way to get 'balance' is to make a calculation based on the latest balance. I have tried using the window function for this. However I haven't been able to make it work the way I want to.

For example, if I write this part of the select statement like below:

SELECT
latest_balance
- (ISNULL (SUM(deposit) OVER(PARTITION BY account ORDER BY date DESC ROWS UNBOUNDED PRECEDING),0)
   + ISNULL (SUM(withdrawal)) OVER(PARTITION BY account ORDER BY date DESC ROWS UNBOUNDED PRECEDING),0))

This would return something like below:

<✖ How it looks now>

account latest_balance date deposit withdrawal balance
XYZ 1 000 2021-07-16 100 900
XYZ 1 000 2021-07-15 200 700

This is not what I would like to see as the balance for the first row should say 1 000 instead of 900. I tried different conbinations of ROWS, BETWEEN, UNBOUNDED, PRECEDING and FOLLOWING for this but I still can't figure out how to make it work.

Could anyone please share your knowledge and enlighten me? Many thanks! =)

Upvotes: 0

Views: 1745

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You seem to want latest_balance with the cumulative sum of deposit and withdrawal removed up to the previous row.

That would be:

select t.*,
       (latest_balance -
        coalesce(sum(deposit) over (partition by account order by date rows between unbounded preceding and 1 preceding), 0) +
        coalesce(sum(withdrawal) over (partition by account order by date rows between unbounded preceding and 1 preceding), 0)
       ) as balance
from t;

Upvotes: 2

Related Questions