question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked WilliamsJeffreyA-3555 answered

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

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.
60567-balance.png


sql-server-generalsql-server-transact-sql
balance.png (20.6 KiB)
5 |1600 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.

WilliamsJeffreyA-3555 avatar image
1 Vote"
WilliamsJeffreyA-3555 answered

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.

5 |1600 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.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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
· 4
5 |1600 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.

i want Transdate order wise ascending

0 Votes 0 ·

I updated the code to sort by TransDate

1 Vote 1 ·

@TomPhillips-1744 ,
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)  


60635-balance.png


0 Votes 0 ·
balance.png (23.3 KiB)

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.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered TomPhillips-1744 commented

Hi @akhterhussain-3167

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 

60785-image.png


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.




image.png (46.4 KiB)
· 4
5 |1600 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.

Balance is not calculating correctly ,@EchoLiu-msft

60750-balance-out.png


0 Votes 0 ·
balance-out.png (23.6 KiB)

Could you tell me how to get 89444?

Echo

0 Votes 0 ·

@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.

0 Votes 0 ·
Show more comments