Solved

correct Balance

Posted on 2012-03-31
3
557 Views
Last Modified: 2012-04-24
I've transaction table of money movement (credit, Debit) for my customer. the field Balance shows the balance after each operation (deposit , withdraw). sometimes, there  is an incorrect value in balance field. therefore, I'd made correct balance procedure by using for loop. this procedure takes long time to correct the balance. is there an other way to do that without for loop. (faster than for loop).
balance.png
0
Comment
Question by:khaled salem
3 Comments
 
LVL 9

Accepted Solution

by:
macarrillo1 earned 250 total points
ID: 37791935
You could change the table(s) that are doing the calculation to use indexes for the fields used in the where portion of your query.
In other words create an index(es) on your table to optimize your query.
0
 
LVL 24

Assisted Solution

by:jimyX
jimyX earned 250 total points
ID: 37792563
In your place I wouldn't create and store column balance in my database, because each time you amend Credit or Debit it requires the entire table to be updated.
This can be done by creating  a CalcField which is going to show a calculation of "Total Cr - Total Dr" of each line for each particular customer "compID".
You are going to have loop if you need the calculation for each record (every line shows the balance) by running a query to calculate the balance from the first record up to the current one.
There might be loop but the loop you have here runs only to calculate and show but not to iterate through your entire records and amend.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37792876
My doubt is why is it showing incorrect data when the calculation part is correct. Can you put the query you are using?
And what isolation level you are using for?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

776 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