DATA TABLE SUM ISSUE

Andrew Deniel

Total Post:29

Points:203
Posted by  Andrew Deniel
 1077  View(s)
Ratings:
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. Sumit Kesarwani

    Post:378

    Points:2694
    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
    INSERT INTO @Temp
    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
    (
        SELECT 
            *,
            ROW_NUMBER() OVER (
                PARTITION BY Month
                ORDER BY Credit DESC, Debit DESC -- whatever order inside group you need
            ) AS N
         FROM @Temp
    )
    SELECT
        Month,
        Credit, 
        Debit, 
        Balance = CASE WHEN N = 1 THEN Balance ELSE NULL END
    FROM Temp2

    I hope it working fine. 

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!