I know there are multiple variations of this question here, but not in this form. My Ledger table, in a basic form have the following columns.
TransactionID | DateOfEntrydate | TransactionDate | ClientID | TrIsDebit | OpeningBalance | Amount | ClosingBalance
DateOfEntry is a colums which stores time of insert of the transcation. TransactionDate stores only the date of the transaction which could be entered manually, Many transaction can have same TransactionDate so i sort ASC in this order TransactionDate, DateOfEntrydate ,TransactionID
I want to maintain previous and final balances for each row (on insert, delete, or edit). I am storing these balance even though i know it is not advisable because I need to be able to know balance retrospectively i.e i am able to know what were my transactions from date A to Date B and my closing balance after each transaction gives always give me the last closing balance.
Doing this for inserting a current dated transaction is ok and can be done easily by check the last record for that client and getting the closing balance of it as your new opening balance.
I have a problem is when I edit,delete a transaction or inserting a new "back dated" transaction. How do i know after which place to insert and update balances of the rows which comes below them ? I know that this is not possible with SQL query alone.
This time i just want to know the best possible way to do this.
Many people suggest that I should use SUM() of amount( by first check if its a debit or credit ) before the Date A to get my opening balance before Date A, but this would not be advisable of a could be very large table. (currently i am doing this but want to change it to storing balances)
Any suggestions guys?