Create Running Account Balance In SQL Subtracting Transactions
I have a table that looks like this:
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:
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.
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
startdaybalance in the
sum(), that will mean you are taking the difference of
(startdaybalance - transactionamt) and sum it
account#, transaction_date is already in the
partition by, does not make sense for it to appear again in
Please do avoid using keyword as column name or alias (
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key