Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-05-09
7
Medium Priority
?
911 Views
Last Modified: 2006-11-18
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.



0
Comment
Question by:21agdgcf
  • 4
  • 3
7 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 16645573
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
 

Author Comment

by:21agdgcf
ID: 16645615
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
 
LVL 35

Expert Comment

by:David Todd
ID: 16645621
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:21agdgcf
ID: 16645663
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
 

Author Comment

by:21agdgcf
ID: 16645691
Sorry forgot, sql 2000
0
 
LVL 35

Expert Comment

by:David Todd
ID: 16652948
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
 

Author Comment

by:21agdgcf
ID: 16715787
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question