Trans_Date is not coming ascending wise ?

Analyst_SQL 3,576 Reputation points
2021-02-23T13:53:24.227+00:00

In ledger trans_Date is not coming ascending wise ,as i am doing Back Date entries,,

 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,sell_ID int)  
     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)  
          
          
       Declare @startDate date='2021-01-19'   
       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=7 and coac.Level_Four_ID=410101) then concat('Cash Received From',' ','Walking',',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
        when  
        (t.Trans_Type_ID=7 and coac.Level_Four_ID!=410101) then concat('Cash Received From',' ',coac.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
               
        when  
        (t.Trans_Type_ID=8 ) then concat('Sales To',' ',coa.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
               
               
        When   
        (t.Trans_Type_ID=2) then concat(COA.Level_Four_Name ,'  
         From',' ',Coac.Level_Four_Name, '',T.Trans_Amount)  
                
        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  
      
 )  
        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  
     Order By  
           Trans_ID;  

71098-asc.jpg

Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,621 Reputation points
    2021-02-24T02:35:25.997+00:00

    Hi @Analyst_SQL ,

    JeffreyWilliams-3310's proposal is good, but it is not feasible to replace the final code with the code he provided. Please refer to the code below:

    Declare @startDate date='2021-01-19'   
    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=7 and coac.Level_Four_ID=410101) then concat('Cash Received From',' ','Walking',',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
             when  
             (t.Trans_Type_ID=7 and coac.Level_Four_ID!=410101) then concat('Cash Received From',' ',coac.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
             when  
             (t.Trans_Type_ID=8 ) then concat('Sales To',' ',coa.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
             When   
             (t.Trans_Type_ID=2) then concat(COA.Level_Four_Name ,'  
              From',' ',Coac.Level_Four_Name, '',T.Trans_Amount)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;  
    

    Output:
    71320-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.

    1 person found this answer helpful.
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Guoxiong 8,206 Reputation points
    2021-02-23T16:11:08.707+00:00

    Replace the field name TransDate in your queries with Trans_Date and then Order BY Trans_Date instead of Trans_ID.

    1 person found this answer helpful.

  2. Farhan Jamil 421 Reputation points
    2021-02-23T17:35:58.257+00:00

    can you not order it by transaction date

    Your code (slightly modified at the end)

        Declare @startDate date='2021-01-19'   
        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=7 and coac.Level_Four_ID=410101) then concat('Cash Received From',' ','Walking',',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
         when  
         (t.Trans_Type_ID=7 and coac.Level_Four_ID!=410101) then concat('Cash Received From',' ',coac.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
                   
         when  
         (t.Trans_Type_ID=8 ) then concat('Sales To',' ',coa.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)   
                   
                   
         When   
         (t.Trans_Type_ID=2) then concat(COA.Level_Four_Name ,'  
          From',' ',Coac.Level_Four_Name, '',T.Trans_Amount)  
                    
         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

    )
    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  
      Order By  
            TransDate  
    

    71261-image.png

    1 person found this answer helpful.
    0 comments No comments

  3. Jeffrey Williams 1,896 Reputation points
    2021-02-23T20:24:42.967+00:00

    Try changing the final Order By to:

          Order By
                Case When Trans_ID =    0 Then '1900-01-01'
                     When Trans_ID = 9999 Then '9999-12-31'
                     Else TransDate
                 End
              , Trans_ID;
    
    1 person found this answer helpful.

  4. Guoxiong 8,206 Reputation points
    2021-02-24T03:15:23.147+00:00
    1. Replace the field name TransDate in your queries with Trans_Date
    2. Change Trans_Date = Null to Trans_Date = '01/01/1900' in the SELECT statement to get opening balence
    3. Change Trans_Date = Null to Trans_Date = '12/31/9999' in the SELECT statement to calculate the totals
    4. Change ORDER BY Trans_ID to Order BY Trans_Date

    Output:
    71336-image.png

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.