Home > DeveloperSection > Forums > Data Table Sum Issue
Andrew Deniel
Andrew Deniel

Total Post:29

Points:203
Posted on    June-19-2013 2:42 AM

 ADO.Net ADO.Net 
Ratings:


 1 Reply(s)
 766  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. 


Sumit Kesarwani

Total Post:378

Points:2694
Posted on    June-19-2013 7:54 AM

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. 


Don't want to miss updates? Please click the below button!

Follow MindStick