How to get a running balance field on a bank ledger dbase

Hi Experts,

I have a dbase with the following fieldsL

ledger_id, ref, credit, debit, transaction_date, balance

the ledger_id is unique and primary and uses identiy seed.

I need to create either a calculated field or a trigger to calculate the running balance field

ie balance = previous row balance value field  + credit field + debit field  just like a bank statement.

Really need some help on what is the best way of doing this.



21agdgcfAsked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

Where do you need the running balance?

What I mean is, what is the final output? Is it excel? Then use an excel formular? Is it crystal reports? Then do so in crystal reports. This is one heck of a lot easier in those kind of environments.

IF the transactions can be sorted by transaction date - it needs to be a datetime rather than just a date then you could use the following
select
  ledger_id, ref, credit, debit, transaction_date,
  (
  select sum( credit ) - sum( debit )
  from table inn
  where inn.ledger_id = o.ledger_id and inn.transaction_date <= o.transaction_date
  )
from table o
where ...

Just some thoughts ...

Kinda a pig of a calculation though. Means that an index scan at best or table scan at worst for every look-up. Also assumes openning balance of zero.

Regards
  David
0
 
21agdgcfAuthor Commented:
Hi David

Output would probably be to a view based on say a customer_id

This view would then be used in a web app

Thanks

Anthony
0
 
David ToddSenior DBACommented:
Hi Anthony,

Can you get the web app to do the running balance?

What version of SQL? I haven't tried this but maybe a clr function would be the trick.

Regards
  David
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
21agdgcfAuthor Commented:
Hi David
Not easy to get the web app to do this as it will be custom code in vb which I am fairly usless at
I think your suggestion of using the datetime field to sort might work in a view.

So I assume as in your earlier statement I would have to do a select statement with the

  (
  select sum( credit ) - sum( debit )
  from table inn
  where inn.ledger_id = o.ledger_id and inn.transaction_date <= o.transaction_date AS balance
  )

not sure how that would produce a running balance or have I missed something

Thanks
Anthony
0
 
21agdgcfAuthor Commented:
Sorry forgot, sql 2000
0
 
David ToddSenior DBACommented:
Hi Anthony,

What it is doing, is adding up the results of all the previous transactions, and including this one.

That is, the balance as of this transaction is the result of all the previous transactions, and this one.

If you have a large number of transactions, I'd suggest that you archive them every month or week, and select an openning balance for the account, and let the web app add them.

That is, from the view return
ledger_id, transaction_date, debit, credit, period_movement, openning_balance

Period movement is the code we discussed above - for the current /month/week/day - whatever suits best.

Regards
  David
0
 
21agdgcfAuthor Commented:
Hi David

Sorry for the delay, never enough hours in the day and always somebody else who needs it yestereday

I understand the logic here now

Many thanks

Anthony
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.