'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.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)