forum

Home / DeveloperSection / Forums / Data Table Sum Issue

Ask Question

Data Table Sum Issue

Andrew Deniel189519-Jun-2013
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. 
ado.netado.net 
Updated on 19-Jun-2013
Andrew Deniel

Andrew Deniel

Follow

Can you answer this question?

Answer

1 Answers

Sumit Kesarwani

Sumit Kesarwani

19-Jun-2013
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. 

Liked By