Balance Is not calculating Properly

Question

In my ledger report ,Balance value is not calculating properly ,if i do back date entry in system.

I mentioned in red value, that Balance will come and so on....Display ledger in TransDate wise ascending

Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2),Opening_Date date)  
                           
      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,trans_rmk varchar(5))  
      INSERT INTO #tbl_Account_L_Four VALUES(1231,'Abdul Rauf',60000,null)  
      INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',45000,'2021-01-17')  
      INSERT INTO #tbl_Account_L_Four VALUES(1215,'MBL 833968',0,null)  
                           
                          
      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,null)  
      INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2,null)  
      INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3,null)   
      INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5,null)  
        INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2,null)    
            INSERT INTO #tbl_transection VALUES(6,1231,1222,55000,'2021-01-21',2,null)  
               INSERT INTO #tbl_transection VALUES(7,1231,1222,55000,'2021-01-20',2,null)  
      INSERT INTO #tbl_transection VALUES(8,1231,1222,5500,'2021-01-02',4,null)  
              
  
  
  
 Declare @startDate date='2021-01-02'   
 Declare @EndDate date='2021-01-21'   
 Declare @Level_Four_ID int =1222  
                  
 ;With initaltransactions  
   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_Account_L_Four                coa  
      Where coa.Level_Four_ID = @Level_Four_ID and coa.Opening_Date<=@StartDate  
      Union All  
      Select t.Trans_ID, Trans_Type = ty.trans_type_name  
           , TransDate = convert(char(10), t.Trans_Date, 101)  
           , Trans_Remarks =  --(CONCAT( T.Cheque_No ,' ',  T.Cheque_Bank ,' ' ,'Cheque',' Date', ' ' ,T.Cheque_Date, ' ' ,'Branch',' ' ,T.Cheque_Branch , ' ','Rs.',CONVERT(varchar(50), CAST(T.Trans_Amount AS money),1),' ','Received From ' ,COAc.Level_Four_Name ))   as Trans_Remarks  
          Case when (t.trans_type_ID=2 ) then   concat('Rs.',CONVERT(Varchar(50), Cast(T.trans_Amount as money),1),   coac.Level_Four_Name)  
          
   When   
          (t.Trans_Type_ID=2) then concat(COA.Level_Four_Name ,'  
           From',' ',Coac.Level_Four_Name, '',T.Trans_Amount)  
    when (t.Trans_Type_ID=4) then (t.trans_rmk)  
    end  
           , Code = iif(t.Level_Four_ID_C = @Level_Four_ID, t.Level_Four_ID_D, t.Level_Four_ID_C)  
           , Head = iif(t.Level_Four_ID_C = @Level_Four_ID, coa.Level_Four_Name, coac.Level_Four_Name)  
           , Debit = iif(t.Level_Four_ID_D = @Level_Four_ID, t.Trans_Amount, 0.00)  
           , Credit = iif(t.Level_Four_ID_C = @Level_Four_ID, 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_Account_L_Four  coa On coa.Level_Four_ID = t.Level_Four_ID_D  
     Left Join #tbl_Account_L_Four  coac On coac.Level_Four_ID = t.Level_Four_ID_C  
     Where @Level_Four_ID In (t.Level_Four_ID_C, t.Level_Four_ID_D) and t.Trans_Date  <= @EndDate  
             )  
  , transactions as (  
       -- Get opening balance  
     Select Trans_ID = 0, Trans_Type = Null, TransDate = Null  
           , Trans_Remarks = 'Opening', Code = Null, Head = Null  
           , Debit = iif(sum(tn.Debit - tn.Credit)>0, sum(tn.Debit - tn.Credit), 0.00)  
           , Credit = iif(sum(tn.Debit - tn.Credit)<0, -sum(tn.Debit - tn.Credit), 0.00)  
     From initaltransactions  tn  
     WHERE tn.TransDate < @startDate OR tn.Trans_ID = 0  
     UNION ALL  
     Select tn.Trans_ID, tn.Trans_Type, tn.TransDate  
           , tn.Trans_Remarks , tn.Code, tn.Head  
           , tn.Debit, tn.Credit  
     From initaltransactions                              tn  
     WHERE tn.TransDate BETWEEN @startDate AND @EndDate  
     )  
      
  ,cte2  
  as(Select tn.Trans_ID, tn.Trans_Type, tn.TransDate  
           , tn.Trans_Remarks, tn.Code, tn.Head  
           , CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Debit END as Debit  
           , CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Credit END AS 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)  
      
 select * from cte2  
 order by case when Trans_ID =0 then '01/01/1900'  
               when Trans_ID =9999 then '12/31/9999'  
          else TransDate end,Trans_ID;  

Answer 1

The problem is here:

convert(char(10), t.Trans_Date, 101)

If you convert the date to a string, guess what SQL Server will sort it as? Yes, as a string. And with the month first, well you get you ask for.

There is zero reason to convert the date in the query. This should be done in the client according to the user's regional settings.

Answer 2

Trying working from this:

SELECT *, SUM(CASE WHEN Level_Four_ID_C = 1222 THEN -1
                   WHEN Level_Four_ID_D = 1222 THEN 1
                   ELSE 0
               END * Trans_Amount) OVER(ORDER BY Trans_Date, Trans_ID 
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM   #tbl_transection

Answer 3

Hi @Analyst_SQL ,

Could we know the purpose to use 'convert(char(10), t.Trans_Date, 101)' in your query?

As mentioned by other experts, it is not necessary to convert this Trans_Date to one string since later this column will be used in the final sort.

Excluding to replace 'convert(char(10), t.Trans_Date, 101) ' with 't.TransDate', you could also convert this date to specific date format as below:

	 convert(date, t.Trans_Date, 101) TransDate  

Besides, if you have further issue about this issue, you could provide more details.

Thank you for understanding!

Best regards
Melissa

---

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.

Answer 4

@Erland Sommarskog ,

I modify query as per your given solution ,query is working fine ,if i retrieved current year(2021) Data ledger ,but if i retrieved 2020 yearly data ,then it is not ascending Data.Check Below Data

 Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2),Opening_Date date)  
                               
       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,trans_rmk varchar(5))  
       INSERT INTO #tbl_Account_L_Four VALUES(1231,'Abdul Rauf',60000,null)  
       INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',45000,'2021-01-17')  
       INSERT INTO #tbl_Account_L_Four VALUES(1215,'MBL 833968',0,null)  
                               
                              
       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,null)  
       INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2,null)  
       INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3,null)   
       INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5,null)  
         INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2,null)    
             INSERT INTO #tbl_transection VALUES(6,1231,1222,55000,'2021-01-21',2,null)  
                INSERT INTO #tbl_transection VALUES(7,1231,1222,55000,'2021-01-20',2,null)  
       INSERT INTO #tbl_transection VALUES(8,1231,1222,5500,'2021-01-02',4,null)  
	         INSERT INTO #tbl_transection VALUES(9,1231,1222,4200,'2020-12-15',4,null)  
                  
      
      
      
  Declare @startDate date='2020-12-01'   
  Declare @EndDate date='2021-01-21'   
  Declare @Level_Four_ID int =1222  
                      
  ;With initaltransactions  
    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_Account_L_Four                coa  
       Where coa.Level_Four_ID = @Level_Four_ID and coa.Opening_Date<=@StartDate  
       Union All  
       Select t.Trans_ID, Trans_Type = ty.trans_type_name  
            , TransDate = convert(char(10), t.Trans_Date, 101)  
            , Trans_Remarks =  --(CONCAT( T.Cheque_No ,' ',  T.Cheque_Bank ,' ' ,'Cheque',' Date', ' ' ,T.Cheque_Date, ' ' ,'Branch',' ' ,T.Cheque_Branch , ' ','Rs.',CONVERT(varchar(50), CAST(T.Trans_Amount AS money),1),' ','Received From ' ,COAc.Level_Four_Name ))   as Trans_Remarks  
           Case when (t.trans_type_ID=2 ) then   concat('Rs.',CONVERT(Varchar(50), Cast(T.trans_Amount as money),1),   coac.Level_Four_Name)  
              
    When   
           (t.Trans_Type_ID=2) then concat(COA.Level_Four_Name ,'  
            From',' ',Coac.Level_Four_Name, '',T.Trans_Amount)  
     when (t.Trans_Type_ID=4) then (t.trans_rmk)  
     end  
            , Code = iif(t.Level_Four_ID_C = @Level_Four_ID, t.Level_Four_ID_D, t.Level_Four_ID_C)  
            , Head = iif(t.Level_Four_ID_C = @Level_Four_ID, coa.Level_Four_Name, coac.Level_Four_Name)  
            , Debit = iif(t.Level_Four_ID_D = @Level_Four_ID, t.Trans_Amount, 0.00)  
            , Credit = iif(t.Level_Four_ID_C = @Level_Four_ID, 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_Account_L_Four  coa On coa.Level_Four_ID = t.Level_Four_ID_D  
      Left Join #tbl_Account_L_Four  coac On coac.Level_Four_ID = t.Level_Four_ID_C  
      Where @Level_Four_ID In (t.Level_Four_ID_C, t.Level_Four_ID_D) and t.Trans_Date  <= @EndDate  
              )  
   , transactions as (  
        -- Get opening balance  
      Select Trans_ID = 0, Trans_Type = Null, TransDate = Null  
            , Trans_Remarks = 'Opening', Code = Null, Head = Null  
            , Debit = iif(sum(tn.Debit - tn.Credit)>0, sum(tn.Debit - tn.Credit), 0.00)  
            , Credit = iif(sum(tn.Debit - tn.Credit)<0, -sum(tn.Debit - tn.Credit), 0.00)  
      From initaltransactions  tn  
      WHERE tn.TransDate < @startDate OR tn.Trans_ID = 0  
      UNION ALL  
      Select tn.Trans_ID, tn.Trans_Type, tn.TransDate  
            , tn.Trans_Remarks , tn.Code, tn.Head  
            , tn.Debit, tn.Credit  
      From initaltransactions                              tn  
      WHERE tn.TransDate BETWEEN @startDate AND @EndDate  
      )  
          
   ,cte2  
   as(Select tn.Trans_ID, tn.Trans_Type, tn.TransDate  
            , tn.Trans_Remarks, tn.Code, tn.Head  
            , CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Debit END as Debit  
            , CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Credit END AS Credit  
            , Balance = sum(tn.Debit - tn.Credit) OVER(ORDER BY tn.TransDate, tn.Trans_ID   
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  
      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)  
          
  select * from cte2  
  order by case when Trans_ID =0 then '01/01/1900'  
                when Trans_ID =9999 then '12/31/9999'  
           else TransDate end,Trans_ID;