Post reply

Query for subtraction from main total for each record

  • February 15, 2011 at 1:25 am

    #235128

    Hi EXPERTS IAM HAVING THIS QUERY

    select a.U_Amount as 'TOTAL VALUE',b.DocNum as 'INVOICE'

    ,b.DocDate as 'DATE',sum(c.U_ASV) as '***.VALUE'

    ,a.U_Amount-sum(c.U_ASV) as 'BALANCE'

    from [dbo].[@CBS_ODFIA] a,[dbo].[oinv]b,[dbo].[inv1]c

    where b.U_DfiaNo=a.U_DfiaNo

    and b.DocEntry=c.DocEntry

    and a.U_DfiaNo='DF 0710058480'

    group by a.U_DfiaNo,a.U_FileNo,a.U_DDate,a.U_DfiaDate,a.U_Amount,a.U_Qty,

    a.U_Wght,b.U_ArNo,b.DocNum,b.U_TotWe,b.DocDate,A.U_AWght

    ORDER BY b.DocNum asc

    fOR THIS THE OUTPUT IS

    12000000.00299524/12/20092600.0011997400.00

    12000000.00317727/12/20094368.0011995632.00

    12000000.00373107/01/201034142.0011965858.00

    12000000.00432525/12/200923880.0011976120.00

    AND I want this output to be

    TotalValue DocNo Date ***.value Balance

    12000000.00299524/12/2009 2600.00 11997400.00

    12000000.00317727/12/2009 4368.00 11993032.00

    12000000.00373107/01/2010 34142.00 11958890.00

    12000000.00432525/12/2009 23880.00 11935010.00

    my balance for second shuld be (11997400.00 - 4368.00) = 11993032.00

    my balance for third shuld be ( 11993032.00 - 34142.00) = 11958890.00

    how many records are there it shuld go on like that

  • February 15, 2011 at 9:14 pm

    #1287443

    Use your query (without the running balance) to populate a TempTable, apply the correct clustered index in the order you want the running total (decrementing balance) be processed by, and then use the "Quirky Update" to calculate the decrementing balance. Search this site for "Quirky Update".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply