1

I have a table account(ID,TransDate,amount,isDebit,balance). Obviously this is a trimmed down version of my table for sake of clarity. TransDate is the date for which the entry will be made, isDebit is a boolean which tells if this transaction is a DEBIT or CREDIT.

A when a new entry is made it could have a transDate older then the last entry made, so i have to position it in right place and change the balances of all the rows which will be below that position. There I sort the rows by transDate ASC , ID ASC , which gives me the correct order of entries.

Balance will be generated by adding current amount to previous last balance id DEBIT and deducted if current entry is CREDIT.

It has the following last 5 entries sorted by transDate ASC , ID ASC:

account(ID,TransDate,amount,isDebit,balance) as an example

41 | 2011-04-10 | 1000 | 1 | 1000      // as this is the first entry  but not always 

37 | 2011-05-14 | 7500 | 1 | 8500

39 | 2011-05-14 | 6500 | 0 | 2000

46 | 2011-05-15 | 1000 | 1 | 3000

Can anyone generate a query(s):

To make a new entry with following details : <ID auto> | 2011-05-9 | 1000 | 1 | <balance>

the query should find that the new entry position is after row with ID 41 and before row ID 37. and also fetches me rows from Id 41 (as this row's balance will be used to calculate the balance for the new row) and below on which i set the new corrected balances.

Or suggest a new method to implement this kind of "ledger accounting".

karanits
  • 37
  • 2
  • 9

1 Answers1

0

Unfortunately, MySQL has no support for window functions, but you might be able to derive a solution from this previous answer involving stored procedures, as SQL Server's window function support is too limited for this kind of query as well: Calculate a Running Total in SQL Server

Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509