've had the same bank account for more than 20 years. I'd hate to have to wait for them to compute a balance every time.
A balance is one of those things you sometimes just have to maintain directly, even though it's technically redundant.
No, it's not automatically one of those things. I've calculated balances on millions of rows at think time. Hopefully you at least tried it out and only on finding that you could not do it at a speed required by the operations staff that you then resorted to this denormalization.
For banks, etc, they'll usually do something like a 3 month window in a DW where the balance of all that has previously occurred prior to that 3 month window (or year window or whatever... is sometimes a list of balances by year and month which can quickly be summarized). They don't calculate the current balance by running through 20 years of transactions. They use previously "frozen" balances and then calculate what the current transactions are and do the math on the previous balance.
My point was what the OP wrote for the posted problem. It appeared that he was trying to maintain the only balance by modifying it directly. That's just not the right way to do it. You MUST have transactions that support what the balance actually is and, yes, that can be a single balance from previously "Closed" transaction periods that will NEVER again change plus the current transaction in the currently "Open" transaction period.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)