correct Balance

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
khaled salemSoftware DeveloperAsked:
Who is Participating?
 
macarrillo1Connect With a Mentor Commented:
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
 
jimyXConnect With a Mentor Commented:
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
 
TempDBACommented:
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
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.