#1
  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. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    6

    Thumbs up


    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


IMN logo majestic logo threadwatch logo seochat tools logo