Advertise with us

Data Table Sum Issue

Posted by  Andrew Deniel
 1633  View(s)
Rate this:
Hi Expert,

I have a situation.

I have datatable that contains the Credit and Debit columns like this

Month   Credit     Debit
Sep       1422825      0
Oct        0                 1422825
Oct       1695017.5    0
Nov        0         1400000
Nov        0         295018

I want a balance that should be shown like this

Month     Credit     Debit       Balance
Sep       1422825      0
Oct         0        1422825      (1422825 of Credit-1422825 of Debit)=0
Oct       1695017.5    0
Nov         0         1400000     
Nov         0         295018       (1695017.5 of credit-1400000+295018)=0.5

it should be shown on crystal report how to do it.

Thanks in advance. 
  1. Re: Data Table Sum Issue

    Hi Andrew,

    For month, used the data type integer 

    DECLARE @Temp TABLE (Month int, Credit money, Debit money, Balance money)
    DECLARE @RunningTotal money
    SET @RunningTotal = 0
    SELECT Month, Credit, Debit, null
    FROM Datatable
    ORDER BY Month
    UPDATE @Temp
    SET @RunningTotal = Balance = @RunningTotal + Credit - Debit
    FROM @Temp
    SELECT * FROM @Temp
    EDIT (this continues from first step):

    If you need to display total only last record in group (month) then you can use ranking function, like...

    ;WITH Temp2 AS
            ROW_NUMBER() OVER (
                PARTITION BY Month
                ORDER BY Credit DESC, Debit DESC -- whatever order inside group you need
            ) AS N
         FROM @Temp
        Balance = CASE WHEN N = 1 THEN Balance ELSE NULL END
    FROM Temp2

    I hope it working fine.