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

[karanits]

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?

[ChrisPadgham]

your use of transactionid as a sort criteria will cause you problems since the transaction you add will have a transactionid out of sequence. I suggest you use date and time rather than just date that way you can select all transactions greater that the date/time of the inserted transaction.

[smantscheff]

You need some sort of chaining of the records. Add a field like NextTransactionID or PreviousTransactionID which will allow you to insert and delete records without breaking any chains.

[karanits]

@cris thanks for a reply. I know, thats why i wrote "i sort ASC in this order TransactionDate, DateOfEntrydate ,TransactionID" in my post which would always give me the correct order of transaction for a client. right?

@sman good point there. Will consider it too.

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

[karanits]

@sman can you make a sql query to impliment this is an existing table( take the above example as a base, if you like)? thanks