CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Lajith
Lajith

Reputation: 1867

query on mutiple transaction on same day

I am using sql server 2012..i have to get running balance on each row..now i am using below query

 CREATE TABLE #Test(ID INT,TransDate datetime,Credit MONEY,[Debit] money)
        INSERT INTO #Test
        SELECT 1,'01/01/2017',10000,NULL UNION    
        SELECT 1,'01/05/2017',40000,NULL UNION
        SELECT 1,'01/05/2017',200,NULL UNION
        SELECT 1,'01/05/2017',200,NULL UNION   
        SELECT 1,'01/09/2017',NULL,45000  UNION
        SELECT 2,'01/05/2017',1000,NULL UNION
        SELECT 2,'01/06/2017',1000,NULL 


        SELECT t1.ID,convert(varchar,t2.TransDate,103) 'date', 
        t2.Credit, 
        t2.Debit, 
        SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
        FROM #Test t1 
        INNER JOIN #Test t2
            ON t1.TransDate <= t2.TransDate

        GROUP BY t1.ID,t2.TransDate  , t2.Credit, t2.Debit

Output:

1   01/01/2017  10000.00    NULL    10000.00
1   05/01/2017  200.00      NULL    50200.00
1   05/01/2017  1000.00     NULL    50200.00
1   05/01/2017  40000.00    NULL    50200.00
1   06/01/2017  1000.00     NULL    50200.00
1   09/01/2017  NULL      45000.00   5200.00
2   05/01/2017  200.00      NULL     1000.00
2   05/01/2017  1000.00     NULL     1000.00
2   05/01/2017  40000.00    NULL     1000.00
2   06/01/2017  1000.00      NULL    2000.00
2   09/01/2017  NULL      45000.00   2000.00

Require Output:

Output:

1   01/01/2017  10000.00    NULL    10000.00
1   05/01/2017  200.00      NULL    10200.00
1   05/01/2017  1000.00     NULL    11200.00
1   05/01/2017  40000.00    NULL    50200.00
1   06/01/2017  1000.00     NULL    51200.00
1   09/01/2017  NULL      45000.00   5200.00
2   05/01/2017  200.00      NULL      200.00
2   05/01/2017  1000.00     NULL     1200.00
2   05/01/2017  40000.00    NULL     41200.00
2   06/01/2017  1000.00      NULL    42200.00

But if multiple entries for same tran day query does not work(see 2nd ,3rd & 4th row result).. it should not same ..Please any one help me to get the proper result?

Upvotes: 2

Views: 1057

Answers (2)

JohnHC
JohnHC

Reputation: 11205

Use a window function. This will work if the date column contains datetime...

select id, 
       TransDate, 
       credit, 
       debit, 
       sum(coalesce(credit,0)-coalesce(debit,0)) 
           over (partition by id order by TransDate) as RunningBalance
from #Test

If the date column is date only, use:

with t1 as
(
select id, 
       TransDate, 
       credit, 
       debit, 
       row_number() over(partition by id, Transdate order by credit) as t_ord
from #Test
)
select id, 
       TransDate, 
       credit, 
       debit,
       sum(coalesce(credit,0)-coalesce(debit,0)) 
           over (partition by id order by TransDate, t_ord) as RunningBalance
from t1

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

First get date with required column and grop by date:

select A.TransDate, SUM(COALESCE(credit, 0)) as credit, SUM(COALESCE(debit, 0)) as debit, SUM(COALESCE(credit, 0) - COALESCE(debit, 0)) AS Balance from
(
SELECT convert(varchar,t2.TransDate, 103) as TransDate,  t2.Credit,     t2.Debit  
FROM Test t1 
INNER JOIN Test t2
    ON t1.TransDate <= t2.TransDate
) as  A group by A.TransDate

=================Or===============

With temp table:

select A.TransDate, SUM(COALESCE(credit, 0)) as credit, SUM(COALESCE(debit, 0)) as debit, SUM(COALESCE(credit, 0) - COALESCE(debit, 0)) AS Balance from
(
SELECT convert(varchar,t2.TransDate, 103) as TransDate,  t2.Credit,     t2.Debit  
FROM Test #t1 
INNER JOIN #Test t2
    ON t1.TransDate <= t2.TransDate
) as  A group by A.TransDate

Upvotes: 1

Related Questions