question

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

Trans_Date is not coming ascending wise ?

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


sql-server-generalsql-server-transact-sql
asc.jpg (104.7 KiB)
· 2
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.

Not clear what you mean? Which value for the marked record do you expect?

0 Votes 0 ·
akhterhussain-3167 avatar image akhterhussain-3167 OlafHelper-2800 ·

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.

0 Votes 0 ·
EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered ·

Hi @akhterhussain-3167,

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.



image.png (15.9 KiB)
· Share
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.

GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered ·

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

· 4 · Share
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.

i have done this,but bottom total is coming on top,which should not come
71252-asc.jpg


0 Votes 0 ·
asc.jpg (71.2 KiB)
GuoxiongYuan-7218 avatar image GuoxiongYuan-7218 akhterhussain-3167 ·

Maybe change Trans_Date = Null to Trans_Date = '12/31/9999' in the last SELECT statement?

1 Vote 1 ·
akhterhussain-3167 avatar image akhterhussain-3167 GuoxiongYuan-7218 ·

Not working,Opening is coming on Bottom
71243-asc.jpg


0 Votes 0 ·
asc.jpg (75.6 KiB)
Show more comments
FarhanJamil-5363 avatar image
1 Vote"
FarhanJamil-5363 answered ·

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



image.png (18.2 KiB)
· Share
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.

JeffreyWilliams-3310 avatar image
1 Vote"
JeffreyWilliams-3310 answered ·

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 · Share
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.

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.

1 Vote 1 ·
GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered ·
  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



image.png (16.4 KiB)
· Share
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.