Balance is not calculating correctly,when date get change in transection.

Question

Balance is not calculating correctly,when date get change in transection.

Analyst_SQL 3,576

Below is data in which two different date are coming,01/19/2021 and 01/18/2021 ,
Date 01/19/2021 transection amount is not getting minus (Credit ) From Balance ,but 01/18/2021 amount is getting Less from balance.
Secondly,i want ,Order by trans_date asc

  Create table #tbl_COA (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2))  
           
  Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))  
  Create table #tbl_transection (Trans_ID int,Level_Four_ID_C int,Level_Four_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int)  
  INSERT INTO #tbl_COA VALUES(1231,'Abdul Rauf',60000)  
  INSERT INTO #tbl_COA VALUES(1222,'Cheque In Hand',45000)  
  INSERT INTO #tbl_COA VALUES(1215,'MBL 833968',0)  
           
          
  insert into #tbl_trans_type VALUES(1,'Online')  
  insert into #tbl_trans_type VALUES(2,'Cheque')  
  insert into #tbl_trans_type VALUES(3,'Deposite')  
  insert into #tbl_trans_type VALUES(4,'Tranfer')  
  insert into #tbl_trans_type VALUES(5,'Return')  
          
  INSERT INTO #tbl_transection VALUES(1,1231,1222,55000,'2021-01-18',2)  
  INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2)  
  INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3)   
  INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5)  
    INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2)    
          
   
 ;WITH cte  
   as(   
   SELECT NULL Trans_ID,NULL Trans_Type,NULL TransDate,'Opening' Trans_Remarks,NULL Code,NULL Head,null Debit,null Credit,isnull(opening,0) Balance   
   from #tbl_COA   
   where Level_Four_ID=1222  
   UNION ALL  
   SELECT T.Trans_ID,ty.trans_type_name as Trans_Type,Convert(varchar, T.Trans_Date ,101)as TransDate,  
   (CONCAT(COA.Level_Four_Name ,' ','Online Receipt C/O',' ',COAc.Level_Four_Name,' ', 'Rs. ',T.Trans_Amount))   as Trans_Remarks,   
   T.Level_Four_ID_C as Code  ,COAc.Level_Four_Name as Head, T.Trans_Amount as Debit, CAST(0 AS decimal(18,2)) as Credit,T.Trans_Amount- CAST(0 AS decimal(18,2)) Balance   
   FROM #tbl_transection T  
   inner  join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D  
   inner join #tbl_COA COAc on COAc.Level_Four_ID=T.Level_Four_ID_C  
   inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID  
   where COA.Level_Four_ID=1222    
   UNION ALL  
   SELECT T.Trans_ID,ty.trans_type_name as Trans_Type, Convert(varchar, T.Trans_Date ,101)as TransDate,  
   (CONCAT(COA.Level_Four_Name ,'Online Receipt C/O',' ',COA.Level_Four_Name, '',T.Trans_Amount))   as Trans_Remarks,  
   T.Level_Four_ID_D as Code,COA.Level_Four_Name as Head, CAST(0 AS decimal(18,2)) as Debit, Trans_Amount as Credit,CAST(0 AS decimal(18,2))-Trans_Amount Balance   
   FROM #tbl_transection T  
   inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D  
   inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID  
   where T.Level_Four_ID_c=1222  
   ),cte1 as (  
   SELECT Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,sum(Balance) over (order by (select null) ROWS UNBOUNDED PRECEDING) Balance  
   FROM cte)  
   select * from cte1   
   UNION ALL  
   SELECT  NULL ,NULL ,NULL , null,null, 'Total',sum(Debit),sum(Credit),null  
   from cte1 order by Head,Trans_ID

Output.

Accepted answer

2 additional answers

Your answer

Answer 1

As posted on SQL Server Central - here is one solution:

Declare @ledgerID int = 1222;  
  
   With transactions  
     As(  
 Select Trans_ID = 0  
      , Trans_Type = Null  
      , TransDate = Null  
      , Trans_Remarks = 'Opening'  
      , Code = Null  
      , Head = Null  
      , Debit = iif(coa.Opening > 0, coa.Opening, 0.00)  
      , Credit = iif(coa.Opening < 0, -coa.Opening, 0.00)  
   From #tbl_COA                coa  
  Where coa.Level_Four_ID = @ledgerID  
  
  Union All  
  
 Select t.Trans_ID  
      , Trans_Type = ty.trans_type_name  
      , TransDate = convert(char(10), t.Trans_Date, 101)  
      , Trans_Remarks = concat(coa.Level_Four_Name, ' ', 'Online Receipt C/O', ' ', coac.Level_Four_Name, ' ', iif(t.Level_Four_ID_C = @ledgerID, '', 'Rs. '), t.Trans_Amount)  
      , Code = iif(t.Level_Four_ID_C = @ledgerID, t.Level_Four_ID_D, t.Level_Four_ID_C)  
      , Head = iif(t.Level_Four_ID_C = @ledgerID, coa.Level_Four_Name, coac.Level_Four_Name)  
      , Debit = iif(t.Level_Four_ID_D = @ledgerID, t.Trans_Amount, 0.00)  
      , Credit = iif(t.Level_Four_ID_C = @ledgerID, t.Trans_Amount, 0.00)  
   From #tbl_transection                           t  
  Inner Join #tbl_trans_type                      ty On ty.trans_type_ID = t.Trans_Type_ID  
   Left Join #tbl_COA                            coa On coa.Level_Four_ID = t.Level_Four_ID_D  
   Left Join #tbl_COA                           coac On coac.Level_Four_ID = t.Level_Four_ID_C  
  Where @ledgerID In (t.Level_Four_ID_C, t.Level_Four_ID_D)  
        )  
 Select tn.Trans_ID  
      , tn.Trans_Type  
      , tn.TransDate  
      , tn.Trans_Remarks  
      , tn.Code  
      , tn.Head  
      , tn.Debit  
      , tn.Credit  
      , Balance = sum(tn.Debit - tn.Credit) over(Order By tn.Trans_Id)  
   From transactions                              tn  
     
  Union All  
    
 Select Trans_ID = 9999  
      , Trans_Type = Null  
      , Trans_Date = Null  
      , Trans_Remarks = 'Total'  
      , Code = Null  
      , Head = Null  
      , Debit = sum(tn.Debit)  
      , Credit = sum(tn.Credit)  
      , Balance = sum(tn.Debit) - sum(tn.Credit)  
   From transactions                              tn  
  Order By  
        Trans_ID;

I would think ordering by the Trans_ID would be appropriate - since it doesn't seem possible to enter a transaction for a prior date at a later date. This appears to be a ledger and you cannot enter transactions for prior dates in a ledger, you enter a new transaction with a new date (and thus a new Trans_ID) for any adjustments.

If you really need to order by date - then you need to account for header/trailer rows and don't convert the dates to strings. One way would be to set a default opening date and a default closing date - for example: 1900-01-01 for opening and 9999-12-31 for closing. Also - instead of converting to date you want the actual date and time of the transaction.

You can always null out the 'default' dates for display.

Answer 2

Your problems is your output is "unsorted", which is causing the balance to not reflect top down as you expect.

Try this:
DROP TABLE IF EXISTS #tbl_COA;
DROP TABLE IF EXISTS #tbl_trans_type;
DROP TABLE IF EXISTS #tbl_transection;

     Create table #tbl_COA (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2))

     Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))
     Create table #tbl_transection (Trans_ID int,Level_Four_ID_C int,Level_Four_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int)
     INSERT INTO #tbl_COA VALUES(1231,'Abdul Rauf',60000)
     INSERT INTO #tbl_COA VALUES(1222,'Cheque In Hand',45000)
     INSERT INTO #tbl_COA VALUES(1215,'MBL 833968',0)


     insert into #tbl_trans_type VALUES(1,'Online')
     insert into #tbl_trans_type VALUES(2,'Cheque')
     insert into #tbl_trans_type VALUES(3,'Deposite')
     insert into #tbl_trans_type VALUES(4,'Tranfer')
     insert into #tbl_trans_type VALUES(5,'Return')

      INSERT INTO #tbl_transection VALUES(1,1231,1222,55000,'2021-01-18',2)
      INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2)
      INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3) 
      INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5)
        INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2) 
          INSERT INTO #tbl_transection VALUES(6,1222,1231,44444,'2020-12-05',2)  

    ;WITH cte
      as( 
      SELECT 1 as Trans_Group, NULL Trans_ID,NULL Trans_Type,CAST(NULL AS date) TransDate,'Opening' Trans_Remarks,NULL Code,NULL Head,null Debit,null Credit,isnull(opening,0) Balance 
      from #tbl_COA 
      where Level_Four_ID=1222
      UNION ALL
      SELECT 2 as Trans_Group, T.Trans_ID,ty.trans_type_name as Trans_Type, CAST(Convert(varchar, T.Trans_Date ,101) AS DATE) as TransDate,
      (CONCAT(COA.Level_Four_Name ,' ','Online Receipt C/O',' ',COAc.Level_Four_Name,' ', 'Rs. ',T.Trans_Amount))   as Trans_Remarks, 
      T.Level_Four_ID_C as Code  ,COAc.Level_Four_Name as Head, T.Trans_Amount as Debit, CAST(0 AS decimal(18,2)) as Credit,T.Trans_Amount- CAST(0 AS decimal(18,2)) Balance 
      FROM #tbl_transection T
      inner  join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D
      inner join #tbl_COA COAc on COAc.Level_Four_ID=T.Level_Four_ID_C
      inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID
      where COA.Level_Four_ID=1222  
      UNION ALL
      SELECT 2 as Trans_Group, T.Trans_ID,ty.trans_type_name as Trans_Type, Convert(varchar, T.Trans_Date ,101)as TransDate,
      (CONCAT(COA.Level_Four_Name ,'Online Receipt C/O',' ',COA.Level_Four_Name, '',T.Trans_Amount))   as Trans_Remarks,
      T.Level_Four_ID_D as Code,COA.Level_Four_Name as Head, CAST(0 AS decimal(18,2)) as Debit, Trans_Amount as Credit,CAST(0 AS decimal(18,2))-Trans_Amount Balance 
      FROM #tbl_transection T
      inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D
      inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID
      where T.Level_Four_ID_c=1222
      ),cte1 as (
      SELECT Trans_Group,Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,sum(Balance) over (order by TransDate, Trans_ID) Balance
      FROM cte)
      select * from cte1 
      UNION ALL
      SELECT 99999 AS Trans_Group, NULL ,NULL ,NULL , null,null, 'Total',sum(Debit),sum(Credit),null
      from cte1 
      ORDER BY Trans_Group,TransDate,Trans_ID

Analyst_SQL 3,576 Reputation points

2021-01-26T16:16:06.27+00:00

i want Transdate order wise ascending
Tom Phillips 17,771 Reputation points

2021-01-26T16:40:45.95+00:00

I updated the code to sort by TransDate
Analyst_SQL 3,576 Reputation points

2021-01-26T19:04:08.99+00:00

@Tom Phillips ,
I have inserted one more row, with previous data ,but that entry didn't come date wise ascending order

INSERT INTO #tbl_transection VALUES(1,1231,1222,55000,'2021-01-18',2) INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2) INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3) INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5) INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2) INSERT INTO #tbl_transection VALUES(6,1222,1231,44444,'2020-12-05',2)
Tom Phillips 17,771 Reputation points

2021-01-26T20:18:31.013+00:00

I updated the code to work. Your code was converting Trans_Date to a string. It was properly sorting the string value.

This is the problem with adding header/footer rows.

Answer 3

Hi @Analyst_SQL ，

Please try：

      ;WITH cte  
        as(   
        SELECT NULL Trans_ID,NULL Trans_Type,NULL TransDate,'Opening' Trans_Remarks,NULL Code,NULL Head,null Debit,null Credit,isnull(opening,0) Balance   
        from #tbl_COA   
        where Level_Four_ID=1222  
        UNION ALL  
        SELECT T.Trans_ID,ty.trans_type_name as Trans_Type,Convert(varchar, T.Trans_Date ,101)as TransDate,  
        (CONCAT(COA.Level_Four_Name ,' ','Online Receipt C/O',' ',COAc.Level_Four_Name,' ', 'Rs. ',T.Trans_Amount))   as Trans_Remarks,   
        T.Level_Four_ID_C as Code  ,COAc.Level_Four_Name as Head, T.Trans_Amount as Debit, CAST(0 AS decimal(18,2)) as Credit,T.Trans_Amount- CAST(0 AS decimal(18,2)) Balance   
        FROM #tbl_transection T  
        inner  join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D  
        inner join #tbl_COA COAc on COAc.Level_Four_ID=T.Level_Four_ID_C  
        inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID  
        where COA.Level_Four_ID=1222    
        UNION ALL  
        SELECT T.Trans_ID,ty.trans_type_name as Trans_Type, Convert(varchar, T.Trans_Date ,101)as TransDate,  
        (CONCAT(COA.Level_Four_Name ,'Online Receipt C/O',' ',COA.Level_Four_Name, '',T.Trans_Amount))   as Trans_Remarks,  
        T.Level_Four_ID_D as Code,COA.Level_Four_Name as Head, CAST(0 AS decimal(18,2)) as Debit, Trans_Amount as Credit,CAST(0 AS decimal(18,2))-Trans_Amount Balance   
        FROM #tbl_transection T  
        inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D  
        inner join #tbl_trans_type ty  on ty.trans_type_ID=T.Trans_Type_ID  
        where T.Level_Four_ID_c=1222  
        ),cte1 as (  
        SELECT Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,sum(Balance) over (order by (select null) ROWS UNBOUNDED PRECEDING) Balance  
        FROM cte)  
        ,cte2 as  
     (select * from cte1   
        UNION ALL  
        SELECT  NULL ,NULL ,NULL , null,null, 'Total',sum(Debit),sum(Credit),null  
        from cte1)  
      
     SELECT * FROM cte2  
     order by IIF(Head='Total',1,0),cast(TransDate as date),Trans_ID

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Analyst_SQL 3,576 Reputation points

2021-01-27T04:26:25.167+00:00

Balance is not calculating correctly ,@EchoLiu-MSFT
EchoLiu-MSFT 14,621 Reputation points

2021-01-27T05:58:46.25+00:00

Could you tell me how to get 89444?

Echo
Analyst_SQL 3,576 Reputation points

2021-01-27T07:03:57.787+00:00

@EchoLiu-MSFT
Opening Balance Will be Subtract from Credit ,sorry i defined above image in Plus in Second Row Balance.
Second row Balance will be(450000-44444)=556

Note: where Debit amount come ,it will add and where Credit amount come it will get subtract.
Tom Phillips 17,771 Reputation points

2021-01-27T17:03:07.027+00:00

Is the code I posted, not working?

I fixed the date sorting problem.

Share via

Balance is not calculating correctly,when date get change in transection.

2 additional answers

Your answer