Click here to Skip to main content
16,004,587 members
Search within:


Select query works fine, but data showing haphazardly how to use order by? Please help me
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
Visual-Studio
, +
Select query works fine, but data showing haphazardly how to use order by? please help me Date wise not show
My query is
alter proc [dbo].[P_GetLgrRptByPartyID]                    
--@acid int,                    
@LegID int,                    
@Sdate date,                                    
@EDate Date                    
As                    
BEGIN                    
  
  
--create table ##temp6 (id [int] IDENTITY(1,1) NOT NULL  ,Date Date,    
--PURTICULARS varchar (50), Vou_no varchar (50), Vou_Type varchar (50),  
--vOUCHERVREF_NO varchar (50),   
--DR_Amount decimal(18, 2),   
--CR_Amount decimal(18, 2),   
--nrr nvarchar(255))  
  
--insert into ##temp6(Date, PURTICULARS, Vou_no, Vou_Type, vOUCHERVREF_NO, DR_Amount, CR_Amount, nrr)  
  
  
select convert(varchar(15),Invdt,105) AS Date,'SALE A/c'     AS PURTICULARS,invid AS Vou_no,case TM_type when 'S'  then 'SALE' else 'SALE' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, invamt as DR_Amount,'0.00'AS CR_Amount  ,nrr from Tbl_Trade_Msater 
  
where partyID=@LegID AND TM_type='S' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate                    
union all                    
select convert(varchar(15),Invdt,105) AS Date,'PURCHASE A/c' AS pURTICULARS,invid AS Vou_no,case TM_type when 'P'  then 'PURCHASE' else 'PURCHASE' end AS Vou_Type, R_invno AS vOUCHERVREF_NO ,'0.00'AS DR_Amount  ,invamt as CR_Amount,nrr               
from Tbl_Trade_Msater where partyID=@LegID AND TM_type='P' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate                    
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,'S.RETURN A/c' AS PURTICULARS,invid AS Vou_no,case TM_type when 'SR' then 'SALE-RETURN' else 'SALE-RETURN' end AS Vou_Type,'N/A' AS vOUCHERVREF_NO,'0.00'AS DR_Amount  , invamt as CR_Amount,nrr               
from Tbl_Trade_Msater where partyID=@LegID AND TM_type='SR' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate                    
union all                    
select convert(varchar(15),Invdt,105) AS Date,'P.RETURN A/c' AS pURTICULARS,invid AS Vou_no,case TM_type when 'PR' then 'PURCHASE-RETURN' else 'PURCHASE-RETURN' end AS Vou_Type,'N/A' AS vOUCHERVREF_NO,invamt as DR_Amount,'0.00'AS CR_Amount ,nrr           
  
    
from Tbl_Trade_Msater where partyID=@LegID AND TM_type='PR' AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate                    
UNION ALL        
                  
select convert(varchar(15),VDate,105) AS Date,PL2.ladgerName, VID AS Vou_no,case VType when 'REC' then 'RECEIPT' else 'RECEIPT' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,'0.00' AS DR_Amount ,Amount as CR_Amount,AV.nrr from Tbl_Ac_Voucher AS AV             
  
       
INNER JOIN tbl_party_ldg AS PL2   ON av.AcID  =PL2.partyID                                    
where LegID=@LegID and VType='REC' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate                    
                    
UNION ALL                    
select convert(varchar(15),VDate,105) AS Date,PL3.ladgerName, VID AS Vou_no,case VType when 'REC' then 'RECEIPT' else 'RECEIPT' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO, Amount AS DR_Amount, '0.00'as CR_Amount,AV.nrr from Tbl_Ac_Voucher AS AV             
  
    
       
       
INNER JOIN tbl_party_ldg AS PL3   ON av.LegID  =PL3.partyID                                    
where AcID=@LegID and VType='REC' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate                    
                    
UNION ALL                    
select convert(varchar(15),VDate,105) AS Date,PL4.ladgerName, VID AS Vou_no,case VType when 'PAY' then 'PAYMENT' else 'PAYMENT' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,Amount as DR_Amount,'0.00'AS CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV              
  
    
       
INNER JOIN tbl_party_ldg AS PL4   ON av.AcID  =PL4.partyID                                    
where LegID=@LegID and VType='PAY' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate                    
                    
UNION ALL                    
select convert(varchar(15),VDate,105) AS Date,PL5.ladgerName, VID AS Vou_no,case VType when 'PAY' then 'PAYMENT' else 'PAYMENT' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,'0.00' as DR_Amount,Amount AS CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV             
  
    
       
INNER JOIN tbl_party_ldg AS PL5   ON av.LegID  =PL5.partyID                                    
where AcID=@LegID and VType='PAY' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate                    
                                 
                    
UNION ALL                    
select convert(varchar(15),VDate,105) AS Date,PL6.ladgerName, VID AS Vou_no,case VType when 'CON' then 'CONTRA' else 'CONTRA' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,Amount as DR_Amount,'0.00'AS CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV                
  
    
     
INNER JOIN tbl_party_ldg AS PL6   ON av.LegID  =PL6.partyID                                    
where AcID=@LegID and VType='CON' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate                    
UNION ALL                    
select convert(varchar(15),VDate,105) AS Date,PL7.ladgerName, VID AS Vou_no,case VType when 'CON' then 'CONTRA' else 'CONTRA' end AS Vou_Type,VRefNo AS vOUCHERVREF_NO,'0.00'AS DR_Amount,Amount as CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV                
  
    
     
INNER JOIN tbl_party_ldg AS PL7   ON av.AcID  =PL7.partyID                                    
where LegID=@LegID and VType='CON' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate                    
UNION ALL                    
  
select convert(varchar(15),VDate,105) AS Date,PL8.ladgerName, VID AS Vou_no,case VType when 'JUN' then 'JOURNAL' else 'JOURNAL' end   
AS Vou_Type,VRefNo AS vOUCHERVREF_NO,'0.00'AS DR_Amount,Amount as CR_Amount ,AV.nrr from Tbl_Ac_Voucher AS AV    
       
INNER JOIN tbl_party_ldg AS PL8   ON av.AcID  =PL8.partyID  
              
where LegID=@LegID and VType='JUN' AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate              
UNION ALL                   
select convert(varchar(15),VDate,105) AS Date,PL9.ladgerName, VID AS Vou_no,case VType when 'JUN' then 'JOURNAL' else 'JOURNAL' end  
AS Vou_Type,VRefNo AS vOUCHERVREF_NO,Amount as DR_Amount, '0.00'AS CR_Amount,AV.nrr from Tbl_Ac_Voucher AS AV  
    
      
INNER JOIN tbl_party_ldg AS PL9   ON av.LegID  =PL9.partyID                                    
where AcID=@LegID and VType='JUN'  AND CAST(VDate AS DATE) BETWEEN @Sdate AND @EDate             
                 
 /*-------------------------------- For Discount -----------------------------------------------------------------*/              
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL10.ladgerName, invid AS Vou_no,case TM_type when 'S'  then 'SALE' else 'SALE' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,'0.00'  as DR_Amount, totaldis AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV  
  
    
               
INNER JOIN tbl_party_ldg AS PL10   ON av.dis_ldr_id  =PL10.partyID                                    
where dis_ldr_id=@LegID AND TM_type='S' AND totaldis!=0.00 AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate                    
                 
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL11.ladgerName, invid AS Vou_no,case TM_type when 'P'  then 'PURCHASE' else 'PURCHASE' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, totaldis as DR_Amount,'0.00' AS CR_Amount,AV.nrr from Tbl_Trade_Msater 
  
    
AS AV                     
INNER JOIN tbl_party_ldg AS PL11   ON av.dis_ldr_id  =PL11.partyID                                    
where dis_ldr_id=@LegID AND TM_type='P' AND totaldis!=0.00 AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate                    
                 
/*---------------------------------------------------------------------------------------*/              
              
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL12.ladgerName, invid AS Vou_no,case TM_type when 'SR' then 'SALE-RETURN' else   
'SALE-RETURN' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,  totaldis as DR_Amount, '0.00' AS CR_Amount,AV.nrr from   
Tbl_Trade_Msater AS AV                     
INNER JOIN tbl_party_ldg AS PL12   ON av.dis_ldr_id  =PL12.partyID                                 
where dis_ldr_id=@LegID AND TM_type='SR' AND totaldis!=0.00 AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate                    
                 
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL13.ladgerName, invid AS Vou_no,case TM_type when 'PR' then 'PURCHASE-RETURN' else 'PURCHASE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,  '0.00' as DR_Amount,totaldis AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV                     
INNER JOIN tbl_party_ldg AS PL13   ON av.dis_ldr_id  =PL13.partyID                                    
where dis_ldr_id=@LegID AND TM_type='PR' AND totaldis!=0.00 AND CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate                
                  
                  
/*-------------------------------- For Round_Up -----------------------------------------------------------------*/      
  --Sale           
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL14.ladgerName, invid AS Vou_no,case TM_type when 'S'  then 'SALE' else 'SALE' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, av.RuAmtD as DR_Amount, '0.00' AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS    
 
AV         
INNER JOIN tbl_party_ldg AS PL14   ON av.RoundUpLdrID  =PL14.partyID                                    
where RoundUpLdrID=@LegID AND TM_type='S' AND  CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate  and RuAmtD!=0.00 --or RuAmtC!=0.00    
    
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL14.ladgerName, invid AS Vou_no, case TM_type when 'S'  then 'SALE' else 'SALE' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, '0.00' as DR_Amount, RuAmtC AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS     
AV         
INNER JOIN tbl_party_ldg AS PL14   ON av.RoundUpLdrID  =PL14.partyID                                    
where RoundUpLdrID=@LegID AND TM_type='S' AND  CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate  and  RuAmtC!=0.00    
    
 --Purchase               
                 
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL14.ladgerName, invid AS Vou_no,case TM_type when 'P'  then 'PURCHASE' else 'PURCHASE' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, av.RuAmtD as DR_Amount, '0.00' AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV                     
INNER JOIN tbl_party_ldg AS PL14   ON av.RoundUpLdrID  =PL14.partyID                                    
where RoundUpLdrID=@LegID AND TM_type='P'  And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate    and RuAmtD!=0.00    
    
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL14.ladgerName, invid AS Vou_no,case TM_type when 'P'  then 'PURCHASE' else 'PURCHASE' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO, '0.00' as DR_Amount, av.RuAmtC   
 AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV                     
INNER JOIN tbl_party_ldg AS PL14   ON av.RoundUpLdrID  =PL14.partyID                                    
where RoundUpLdrID=@LegID AND TM_type='P'  And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate    and  RuAmtC!=0.00    
    
           
--/*---------------------------------------------------------------------------------------*/            
     --- Sale Return        
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL15.ladgerName, invid AS Vou_no ,case TM_type when 'SR' then   
'SALE-RETURN' else 'SALE-RETURN' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,av.RuAmtD as DR_Amount, '0.00' AS CR_Amount,  
AV.nrr from Tbl_Trade_Msater AS AV                     
INNER JOIN tbl_party_ldg AS PL15   ON av.RoundUpLdrID  =PL15.partyID                                    
where RoundUpLdrID=@LegID AND TM_type='SR'  And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate    and RuAmtD!=0.00              
     
 UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL15.ladgerName, invid AS Vou_no,case TM_type when 'SR'  
 then 'SALE-RETURN' else 'SALE-RETURN' end  AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,'0.00' as DR_Amount,   
 RuAmtC AS CR_Amount,AV.nrr from Tbl_Trade_Msater AS AV                     
INNER JOIN tbl_party_ldg AS PL15   ON av.RoundUpLdrID  =PL15.partyID                                    
where RoundUpLdrID=@LegID AND TM_type='SR'  And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate    and  RuAmtC!=0.00          
           
  -- Purchase Return       
        
UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL16.ladgerName, invid AS Vou_no,case TM_type when 'PR' then 'PURCHASE-RETURN' else 'PURCHASE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,av.RuAmtD as DR_Amount, '0.00' AS CR_Amount,AV.nrr from    
 
Tbl_Trade_Msater AS AV                     
INNER JOIN tbl_party_ldg AS PL16   ON av.RoundUpLdrID  =PL16.partyID                                    
where RoundUpLdrID=@LegID AND TM_type='PR'  And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtD!=0.00                
                 
 UNION ALL                    
select convert(varchar(15),Invdt,105) AS Date,PL16.ladgerName, invid AS Vou_no,case TM_type when 'PR' then 'PURCHASE-RETURN' else 'PURCHASE-RETURN' end AS Vou_Type,SaleChallanNo AS vOUCHERVREF_NO,'0.00' as DR_Amount, av.RuAmtC  AS CR_Amount,AV.nrr from   
  
Tbl_Trade_Msater AS AV                     
INNER JOIN tbl_party_ldg AS PL16   ON av.RoundUpLdrID  =PL16.partyID                                    
where RoundUpLdrID=@LegID AND TM_type='PR'  And CAST(Invdt AS DATE) BETWEEN @Sdate AND @EDate and RuAmtC!=0.00                
         
             --select  PURTICULARS, Vou_no, Vou_Type, vOUCHERVREF_NO, DR_Amount, CR_Amount, nrr from ##temp6  order by ##temp6.PURTICULARS asc
   
END                    
                    
    
-- P_GetLgrRptByPartyID '48','2000-04-30','2025-04-26'                
--drop table ##temp6


What I have tried:

--select  PURTICULARS, Vou_no, Vou_Type, vOUCHERVREF_NO, DR_Amount, CR_Amount, nrr from ##temp6  order by ##temp6.PURTICULARS asc



--drop table ##temp6

I directly try 
<pre lang="VB">
order by date ASC but not work
Posted
Add a Solution
Comments
0x01AA 14-Jun-24 9:22am    
Especally for UNIONS it is sometimes better to simply use the 'field number' in the order statement.

E.g. 'SELCT FA, FB, FC FROM XYZ ORDER BY 2' will order by the field value 'FB'
Jayanta Modak 14-Jun-24 9:41am    
Thanks sir for your reply. But if I don't use the Union all then my query not run, and I used your suggestions please help me to overcome from this problem
0x01AA 14-Jun-24 13:37pm    
I don't get the point, sorry.
Does 'ORDER BY #' where # is the field number (1...N) works or not? Btw. 'ORDER BY #' can be used in any SELECT in most SQL Servers, also for not UNION SELECT statements.

1 solution

In the question you mention that the data should be ordered ascending by a date field but in the order by clause you use PURTICULARS which is a varchar field.

If the content of PURTICULARS really is a date then you should change the data type of the column in the temp table to date.

UPDATE:
In your example the table definition is
SQL
create table ##temp6 (id [int] IDENTITY(1,1) NOT NULL  ,Date Date,    
PURTICULARS varchar (50), Vou_no varchar (50), Vou_Type varchar (50),  
vOUCHERVREF_NO varchar (50),   
DR_Amount decimal(18, 2),   
CR_Amount decimal(18, 2),   
nrr nvarchar(255))

Then the problem is defined as follows
Jayanta Modak wrote:
Date wise not show

And the query is

Jayanta Modak wrote:
select PURTICULARS, Vou_no, Vou_Type, vOUCHERVREF_NO, DR_Amount, CR_Amount, nrr from ##temp6 order by ##temp6.PURTICULARS asc


So if the data in column PURTICULARS really is date and you want the ordering to happen based on the date then you should modify the table definition as follows
SQL
create table ##temp6 (id [int] IDENTITY(1,1) NOT NULL  ,Date Date,    
PURTICULARS date, Vou_no varchar (50), Vou_Type varchar (50),  
vOUCHERVREF_NO varchar (50),   
DR_Amount decimal(18, 2),   
CR_Amount decimal(18, 2),   
nrr nvarchar(255))


Also note that since you're selecting only from one table, you don't have to specify the table name in ORDER BY clause. Syntax below should be sufficient
SQL
SELECT PURTICULARS, 
       Vou_no, 
       Vou_Type, 
       vOUCHERVREF_NO, 
       DR_Amount, 
       CR_Amount, 
       nrr 
FROM ##temp6  
ORDER BY PURTICULARS asc
 
Share this answer
 
Posted
Updated 15-Jun-24 5:09am
v3
Comments
Jayanta Modak 15-Jun-24 7:48am    
Sir try with particular but queary not run. Date wise not show
What is data type in 'date' temp table
Wendelius 15-Jun-24 11:30am    
Not sure if I understood your comment, but have a look at the updated answer
Jayanta Modak 16-Jun-24 2:03am    
Sir thanks, for reply
##temp table not work, I try it and sort by every field name of ##temp table. ##temp table is commend out using "-" I want to sort data by date wise please help me. 
create table ##temp6 (
id				int IDENTITY(1,1) NOT NULL,
V_Date			Date ,        
PURTICULARS		varchar (50), 
Vou_no			varchar (50),
Vou_Type		varchar (50),      
vOUCHERVREF_NO	varchar (50),       
DR_Amount		decimal(18, 2),       
CR_Amount		decimal(18, 2),       
nrr				nvarchar(255))  


insert into ##temp6
(
V_Date,
PURTICULARS,
Vou_no, 
Vou_Type, 
vOUCHERVREF_NO, 
DR_Amount, 
CR_Amount,
nrr
)


then my main query
select.......
......
select * from ##temp6
end

Getting Error ----
Msg 241, Level 16, State 1, Procedure P_GetLgrRptByPartyID, Line 21
Conversion failed when converting date and/or time from character string.

Same error when I use 
select * from ##temp6  order by V_Date asc
Wendelius 16-Jun-24 2:58am    
Can you post a few example rows for the data that is inserted into the temp table
Jayanta Modak 16-Jun-24 8:26am    
hello sir,
no data insert in temp table, because V_date column data type is Date in ##temp6 table.
if I change the data type date to varchar(15) then insert data normally like main query, then I sort any field name asc or desc order it works fine except V_date field. in main table date column data type is date but when I use the field for query I change/ convert date to varchar like 
select convert(varchar(15),Invdt,105) AS Date .........
that for V_date field not work order by commend. Please help me
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Saved Access2003 Query Works Fine in Access but not when Run in C#
LINQ order by is nor working with multiple select
How to use mysql SELECT UNION with different ORDER by statements?
This is my query and it is not showing any data please someone help me :(
My ssrs subreports working fine in visual studio where failing in ssrs manager with rrrenderingerror
Please help! Select query in SQL
sorting gridview columns pagewise when we use order by inthe select query
SQL Select Query problem
I use the niwoocommerce plugin to work with order statuses in woocommerce. I want 'pending payment' orders to show up in the store >> all orders page
Hello, I have a SQL query that I would like to save in a variable in order to reuse it later in my code. How to do this please

Advertise
Privacy
Cookies
Terms of Use
Last Updated 15 Jun 2024
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web02 2.8:2024-07-22:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900