SQL statement running balance query with previous balance taken into account

[wezcornell2008]

Hi Guys

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,
(SELECT SUM(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 normal.

Any easy solutions?

Many, many thanks for your help in advance.

All the best

Wesley

[Fetch]

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.