Ad

Create Running Account Balance In SQL Subtracting Transactions

- 1 answer

I have a table that looks like this:

account#Transaction_DateStartDayBalanceTransactionAmtPartition
12012-03-20$500$251
12012-03-20$500$302
12012-03-21$445$251
12012-03-21$445$102
12012-03-21$445$253
22012-03-20$100$51
22012-03-20$100$252

I need to be able to isolate the running balance after each transaction, by account and by day. The research I've done so far on running total assumes what you're trying to add together is in the same column, which it isn't in this case. The "partition" column was not on the original table, I added that in because I was hopeful that would be helpful.

The output I'm trying to achieve is:

account#Transaction_DateStartDayBalanceTransactionAmtPartitionAvail_bal
12012-03-20$500$251$475
12012-03-20$500$302$445
12012-03-21$445$251$420
12012-03-21$445$102$410
12012-03-21$445$253$385
22012-03-20$100$51$95
22012-03-20$100$252$70

I tried something like this, which failed spectacularly:

sum(startdaybalance - transactionamt) over(partition by account#,transaction_date,order by account#,transaction_date) as avail_bal

That ended up summing the remaining balances, which wasn't right.

Then I tried a case when series that got ugly quickly and also didn't work:

set avail_bal = case when partition=1 then (startdaybalance - transactionamt)
                    when partition=2 then (case when partition=1 then (startdaybalance - transactionamt) end) - transactionamt
                    when partition =3 then (case when partition=2 then (startdaybalance - transactionamt) end) - transactionamt end

This also isn't particularly sustainable since I'm dealing with millions of rows of transactions and some days a person had 50+ transactions.

Any guidance here on how to get this "avail_bal" column to act appropriately would be appreciated. This is on SQL Server.

Ad

Answer

You have almost got it, just need to change to

startdaybalance 
- sum(transactionamt) over (partition by account#, transaction_date
                                order by partition_no) as avail_bal

Don't include startdaybalance in the sum(), that will mean you are taking the difference of (startdaybalance - transactionamt) and sum it

Also account#, transaction_date is already in the partition by, does not make sense for it to appear again in order by

Please do avoid using keyword as column name or alias (partition).

Ad
source: stackoverflow.com
Ad