I have a table that looks like this:
| account# | Transaction_Date | StartDayBalance | TransactionAmt | Partition |
|---|---|---|---|---|
| 1 | 2012-03-20 | $500 | $25 | 1 |
| 1 | 2012-03-20 | $500 | $30 | 2 |
| 1 | 2012-03-21 | $445 | $25 | 1 |
| 1 | 2012-03-21 | $445 | $10 | 2 |
| 1 | 2012-03-21 | $445 | $25 | 3 |
| 2 | 2012-03-20 | $100 | $5 | 1 |
| 2 | 2012-03-20 | $100 | $25 | 2 |
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_Date | StartDayBalance | TransactionAmt | Partition | Avail_bal |
|---|---|---|---|---|---|
| 1 | 2012-03-20 | $500 | $25 | 1 | $475 |
| 1 | 2012-03-20 | $500 | $30 | 2 | $445 |
| 1 | 2012-03-21 | $445 | $25 | 1 | $420 |
| 1 | 2012-03-21 | $445 | $10 | 2 | $410 |
| 1 | 2012-03-21 | $445 | $25 | 3 | $385 |
| 2 | 2012-03-20 | $100 | $5 | 1 | $95 |
| 2 | 2012-03-20 | $100 | $25 | 2 | $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.
CodePudding user response：
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).