Trans_Date is not coming ascending wise ?

Question

Trans_Date is not coming ascending wise ?

Analyst_SQL 3,576

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;

Olaf Helper 47,516 Reputation points

2021-02-23T14:35:01.427+00:00

Not clear what you mean? Which value for the marked record do you expect?
Analyst_SQL 3,576 Reputation points

2021-02-23T14:41:38.233+00:00

I am saying ,that Date 01/20/2021 ,come before 01/21/2021,means that Data Display Date wise ascending ,not Trans_ID wise ,currently it is being displayed Trans_ID wise ascending ,but i want Date wise ascending.

Accepted answer

4 additional answers

Your answer

Olaf Helper 47,516 Reputation points

2021-02-23T14:35:01.427+00:00

Not clear what you mean? Which value for the marked record do you expect?
Analyst_SQL 3,576 Reputation points

2021-02-23T14:41:38.233+00:00

I am saying ,that Date 01/20/2021 ,come before 01/21/2021,means that Data Display Date wise ascending ,not Trans_ID wise ,currently it is being displayed Trans_ID wise ascending ,but i want Date wise ascending.

Answer 1

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:

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.

Answer 2

Guoxiong 8,206

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

Analyst_SQL 3,576 Reputation points

2021-02-23T16:27:19.957+00:00

i have done this,but bottom total is coming on top,which should not come
Guoxiong 8,206 Reputation points

2021-02-23T16:51:24.15+00:00

Maybe change Trans_Date = Null to Trans_Date = '12/31/9999' in the last SELECT statement?
Analyst_SQL 3,576 Reputation points

2021-02-23T16:59:54.657+00:00

Not working,Opening is coming on Bottom

Guoxiong 8,206

Just change the last part. Or you can use Trans_Date = '01/01/1900' as the opening date.

Select Trans_ID = 9999
          , Trans_Type = Null
          , Trans_Date = '12/31/9999'
          , 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_Date;

Answer 3

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

Answer 4

Jeffrey Williams 1,896

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;

Guoxiong 8,206 Reputation points

2021-02-24T03:07:15.307+00:00

You will get the error message:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Answer 5

Guoxiong 8,206

Replace the field name TransDate in your queries with Trans_Date
Change Trans_Date = Null to Trans_Date = '01/01/1900' in the SELECT statement to get opening balence
Change Trans_Date = Null to Trans_Date = '12/31/9999' in the SELECT statement to calculate the totals
Change ORDER BY Trans_ID to Order BY Trans_Date

Output:

Share via

Trans_Date is not coming ascending wise ?

4 additional answers

Your answer