1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Dec 2013
Posts
1
Rep Power
0

#### Calculate the balance

I have the following script

select A.Ref, if(A.Nil>0,A.Nil,0) as Debit, if(A.Nil<0,-A.Nil,0) as Credit, A.Nil+(select SUM(B.Nil) from Trial as B where B.Ref<A.Ref) as Balance from Trial as A

The sript to repeat many times, there are 100 examples of such records, will perform repetitions eat almost as much as 10,000 times the query!!!

but if the number of records the more, the more slowly process its query ...

and I have a form that has a script in MySQL optimize queries better, as the script that I write here

select @MEND:=0;

Select @mD:=if(Nil>0,Nil,0) as Debit, @mC:=if(Nil<0,-Nil,0) as Credit, @MEND:=@MEND+@mD-@mC as Balance from trial;

+ -------- + ------- + ------- +
| Debit | Credit | Balance |
+ -------- + ------- + ------- +
| 3434 | 0 | 3434 |
| 3434 | 0 | 6868 |
| 4434 | 0 | 11302 |
| 0 | 544 | 10758 |
| 0 | 453 | 10305 |
+ -------- + ------- + ------- +

Memory usage script variable @ above, when using Firebird SQL, maybe friends of friends can help us solve the above problem.

thank you
Last edited by rito11_96; December 19th, 2013 at 07:15 PM. Reason: correction words
2. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Sep 2008
Posts
74
Rep Power
10

Maybe ONE field CALCULATED can solve your problem!

ex.: fieldDEB - fieldCRED - fieldSALD ( fieldCALC will be fieldSALD, in case)

in handle for fieldCALC (proprerty CALCFIELDS in Dataset):

xxtbFieldCALC := xxtbFieldCALC + (-fieldDEB) + fieldCRED

So, FieldCALC calculate the diference between to fields before.

fieldDEB - fieldCRED - fieldCALC
2000 0 2000
0 1000 1000

I believe that work for you