maintaning opening and closing balance for each row in a ledger table

Question

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 column which stores time of insert of the transaction. 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?

UPDATE I also want to know if I use SUM(), what is the best way to dynamically generate opening and closing balance after each transaction for a client and NOT store it in table for sample data

TransactionID | DateOfEntrydate    | TransactionDate | ClientID | TrIsDebit | Amount
225           | 2012-05-06 18:20:10| 2012-03-01      | 360      | 0         |    100
219           | 2012-05-06 18:09:16| 2012-03-31      | 360      | 1         |   1000
224           | 2012-05-06 18:19:49| 2012-03-31      | 360      | 0         |    100
218           | 2012-05-06 18:08:09| 2012-04-30      | 360      | 1         |   1000
221           | 2012-05-06 18:17:55| 2012-04-30      | 360      | 1         |   1000
222           | 2012-05-06 18:18:58| 2012-04-30      | 360      | 0         |    500
220           | 2012-05-06 18:17:10| 2012-05-01      | 360      | 1         |   1000
223           | 2012-05-06 18:19:28| 2012-05-01      | 360      | 0         |    500

to be displayed as

TransactionID | DateOfEntrydate    | TransactionDate | ClientID | TrIsDebit | "dynamicOpeningBalance" | Amount | "dynamicClosingBalance"
225           | 2012-05-06 18:20:10| 2012-03-01      | 360      | 0         |      0                  |  100   |-100
219           | 2012-05-06 18:09:16| 2012-03-31      | 360      | 1         |   -100                  | 1000   | 900
224           | 2012-05-06 18:19:49| 2012-03-31      | 360      | 0         |    900                  |  100   | 800
218           | 2012-05-06 18:08:09| 2012-04-30      | 360      | 1         |    800                  | 1000   |1800
221           | 2012-05-06 18:17:55| 2012-04-30      | 360      | 1         |   1800                  | 1000   |2800
222           | 2012-05-06 18:18:58| 2012-04-30      | 360      | 0         |   2800                  |  500   |2300
220           | 2012-05-06 18:17:10| 2012-05-01      | 360      | 1         |   2300                  | 1000   |3300
223           | 2012-05-06 18:19:28| 2012-05-01      | 360      | 0         |   3300                  |  500   |2800

Brendon Cheves Brendon Cheves · Accepted Answer · 2012-05-06T15:02:37

So you want to be able to know what the balances were at any given time even if transactions may have been deleted or edited since then? If so, there are several possible approaches.

One would be to keep a separate table of historical balances rather than store them in the same table. This other table would store a date and the balances as of that date. Current balances could be calculated in the query.

Another approach could be to keep to the table as is. But, in this case, use the balance fields to maintain historical balances only. Current balances, again, could be calculated in the query.

Another approach, if you really want to avoid using SUM() in your query (although I'm not sure why) would be to add additional fields to this table that would keep both historical and current balances. This approach, however, would be limited to just a single historical balance.

Also, using SUM() in your query to get current balances should not really be an issue, even in a large table or database, unless I am missing something here.

Please let me know if I misunderstood your question.

maintaning opening and closing balance for each row in a ledger table

1 Answers