I have a SQL statement which caclulates the running balance
for a list of transactions in a transactions table. This SQL
statement is as follows:
SELECT transID, debit, credit,
FROM transactions as D1
WHERE D1.transID <= D0.transID) AS balance
FROM transactions AS D0
The only problem I'm having is that I have to display
transactions between a particular date range. I have for eg.
transID, transDate, debit, credit fields in my db.
However the problem I'm having is that when the transaction
records are pulled out for the specified date range the balances
are only calculated for those records. I need someway of having a
balance b/f (brought forward) so that the selected records use that
as a 'starting' balance and then calculate the running balance as
You'd need to determine the b/f then apply it against the
transaction records when they are pulled for a date range. So if
the date range is
Feb 1st, 2009 -> Feb 28th, 2009
You'd need to also get the previous balance in a separate
query, then use it to correctly display the running balance when
you output the date range.
I suppose a simpler way involving a little more DB work (and
possibly lots of coding) is to store the current balance as well
with each transaction; then when performing a date range you'd have
the balance as well and not have to calculate it on the fly.