Data Table Sum Issue

Question

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.

do you need to show the balance at the end of the month only. As in case of Nov. Or you need to show the balance in every row. — शेखर, Jun 14 '13 at 11:20
This is a running value problem. See [this](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver). It would be helpful if you would state version of SQL server. For examlpe, this is for [2005](http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx). — OzrenTkalcecKrznaric, Jun 14 '13 at 11:21
for shekhar, yes i need balance of every month but infront of debit. — Fahad Mirza, Jun 14 '13 at 11:26

OzrenTkalcecKrznaric · Answer 1 · 2013-06-14T11:44:22.667

0

Here's your solution optimized for 2005, I'm sure it works well on 2008. (use integer for month, it's easier...)

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

This is not tested, code written directly from my head :P

edited Jun 14 '13 at 11:44

answered Jun 14 '13 at 11:30

OzrenTkalcecKrznaric

5,535
4
34
57

Dear Ozren, Thnx for your answer. it is really helpful, but dear i don't want to get the running total, instead i want running balance of every month. please review my question again. Really thnx God Bless You. – Fahad Mirza Jun 14 '13 at 11:33
Your question looks like you need all rows, not one row per month. If you need one row per month then you use common sum + group by Month. – OzrenTkalcecKrznaric Jun 14 '13 at 11:36
Dear Ozren, can i do it using LINQ? – Fahad Mirza Jun 14 '13 at 13:27
Mirza, that was not the question, and I would suggest to open another question for that if it weren't already asked. You can have running values evaluated using LINQ and there is a solution on SO here: [http://stackoverflow.com/questions/1834753/linq-to-sql-and-a-running-total-on-ordered-results](http://stackoverflow.com/questions/1834753/linq-to-sql-and-a-running-total-on-ordered-results). – OzrenTkalcecKrznaric Jun 14 '13 at 13:33

Data Table Sum Issue

1 Answers1