question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked ·

Balance Is not calculating Properly

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;


79809-leder2.jpg


sql-server-generalsql-server-transact-sql
leder2.jpg (127.7 KiB)
· 4
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you describe these tables a little. What is Level Four= What are the two Level_Four ids in the transaction table? The transaction table has eight rows, but the output has ten? And how do you know whether a transaction is credit or debit?

Keep in mind that while we know SQL here, we don't know about your tables and business rules, so those you need to explain to us, so that we can help you?

0 Votes 0 ·

Hi @ErlandSommarskog ,

Your question?
Can you describe these tables a little.


Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2),Opening_Date date)

tbl_Account_L_Four is chart of Account table,in which i opened head,with opening.


What is Level Four= What are the two Level_Four ids in the transaction table?

Level_Four_ID_C int column is used for Credit ,Level_Four_ID_D int column is used for Debit


The transaction table has eight rows,but the output has ten?

Opening row is coming from tbl_Account_L_Four table if exit value,

And how do you know whether a transaction is credit or debit?

in tbl_Tansection table i have defined two column (Level_Four_ID_C int column is used for Credit ,Level_Four_ID_D int column is used for Debit) ,through it i get to know Debit and Credit



0 Votes 0 ·

Excuse me for being dense, but can you explain row by row in the output why something is credit or debit and why you want that balance amount.

1 Vote 1 ·
Show more comments
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ·

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.

· 5 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

True. In fact it is more than just "There is zero reason to convert the date" to string in the server side. There are reasons not to do so. One of these reasons is that by converting the value to string you pass more data from the server to the client. Therefore, even if we put aside the sorting order, in most cases it is better to pass the date as date and not as string.

1 Vote 1 ·

So ,what could be done, for this.my regional settings is karachi,Islamabad ,

Or it will not resolve?

0 Votes 0 ·

Your Regional Settings have no impact on how SQL Server interprets the query. You have converted the date to a string (with a format I would not expect to see in Islamabad), and then it goes downhill from there.

Stop converting the date to a string. Formatting of the data should occur in the client.

1 Vote 1 ·
pituach avatar image pituach akhterhussain-3167 ·

Hi @akhterhussain-3167

From the database aspect, it is not matter what is your region. Dates are stored the same in the server if you store them as data type Date. The only differences are in displaying the data.

Check this post: Date displaying format, vs Date storing format

So ,what could be done, for this.

Remove the CONVERT to string and use Date

Instead of using convert(char(10), t.Trans_Date, 101) use it t.Trans_Date

Note! You also convert T.trans_Amount to MONEY. Why not leave it as Decimal? if you want it to be MONEY then why not configure the column as data type MONEY which will make sure the that the data you insert will actually fit money and will be stored as such?

In addition you convert to string by using CONCAT. You can add column with the text that you want to CONCATE and CONCAT in the client side, Or add the text in the client.







0 Votes 0 ·

Thanks ,i am thinking to change it, recently i faced this issue,that user input value in decimal ,due to it me application is getting crashed,thanks

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ·

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
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 answered ·

@ErlandSommarskog ,

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;


79830-led2.png



led2.png (27.3 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered ·

Hi @akhterhussain-3167,

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.