I have two tables that stores balance and transaction information about an account.
The first table stores all the transaction with transaction amount and effective date. The transaction has a code that differentiates deposit or withdrawal.
The other table stores balance but this balance is not historical. It is a rolling balance. It keeps on updating every time there is a transaction.
There is no place that stores the historical balance information after each transaction.
How do I calculate balance after each transaction in the history table using the current balance and the transaction code? Below is the example…
This is how the balance is stored