Solved

ORDER BY CASE END QUESTIONS - SORT QUESTION

Posted on 2013-11-30
15
236 Views
Last Modified: 2014-01-22
In a VIEW, my existing order by clause is as follows:

ORDER BY CASE WHEN CourierID = 0 THEN 0 ELSE 1 END, CASE WHEN ReqPickupTime IS NOT NULL THEN ReqPickupTime END, WaybillNumber DESC

So Courier ID sorts first if =0, then ReqPickupTime, then WaybillNumber DESC


I want to modify but I am having trouble.

Want to introduce a 4th field "DeadlineTime" and sort as follows:


CourierID=0
ReqPickupTime sort ASC
(null value ReqPickupTime will be last while earliest ReqPickupTime first)

then if CourierID<>0
DeadlineTime sort ASC
(null value DeadlineTime will be last while earliest DeadlineTime first)
0
Comment
Question by:Dale Massicotte
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39687338
What issue you are getting here? Sorting will work using all order by fields.

If you want to use your all fields CourierId, ReqPickupTime - Those are interdependant, then you should split your query and first get the result in temp table.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39687340
Use the ISNULL function to set a NULL value to some dummy number guaranteed to be greater than any actual value.
..., 
CASE WHEN CourierID=0 THEN ISNULL(ReqPickupTime,  '2999-12-31') ELSE ISNULL(DeadlineTime, '2999-12-31') END

Open in new window

0
 

Author Comment

by:Dale Massicotte
ID: 39687367
Thanks jim for helping me write the query but SQL Server cannot parse ERROR
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39687371
>SQL Server cannot parse ERROR
You'll have to spell out exactly what you mean by this.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 39687375
The above also requires that ReqPickupTime and DeadlineTime are of the same data type.
So ... what data type are they?
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39687390
Best and clean solution, As I said earlier, split the query, you will get fast result and readable query.
0
 

Author Comment

by:Dale Massicotte
ID: 39687392
i used this but I am not getting correct results

ORDER BY CASE WHEN CourierID = 0 THEN 0 ELSE 1 END, WaybillNumber DESC,
CASE WHEN CourierID=0 THEN ISNULL(ReqPickupTime,  '2999-12-31') ELSE ISNULL(DeadlineTime, '2999-12-31') END

(it is not sorting the ReqPickupTime and DeadlineTime correctly
0
 

Author Comment

by:Dale Massicotte
ID: 39687394
VjSoft isn't that what JimHorn is trying to show me while you keep saying the same thing and offering nothing?
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39687401
DalerTheWhaler
I can give you query, but for that I will need information of all of the fields, table, also what is the input and output. Without this how can I write something?

As per statment you have give in your question, I just can say that performance will not be good and might be you will not get best results. Also query is not readable

http://blog.sqlauthority.com/2007/07/17/sql-server-case-statement-in-order-by-clause-order-by-using-variable/

http://www.sqlteam.com/article/dynamic-order-by
0
 

Author Comment

by:Dale Massicotte
ID: 39687406
SELECT      TOP 999999 WaybillNumber, CourierID, SendersAcctNo, CourierID2, OrderDate, OrderTime, PickupDate, PickupArrival, PickupTime, DeliveryDate, 
                        DeliveryTime, CompanyName, City, CompanyName2, City2, NoOfPieces, Weight, BillingRef, RecipientsSignature, ReqPickupTime, Cancel, NightRate, 
                        WERate, HolRate, Dist, PickupLate, State, State2, PortA1, AirbillNo, Street, Street2, ReqPickupDate, Miles, DriverAmount, Dept, UserID, Zip, Zip2, 
                        EnableRte, DeliveryArrival, CheckMileage, Closed, GetCharges, ChargesGiven, Posted, Invoiced, Returnz, AddressType1, AddressType2, [Full], 
                        VehicleType, CardTransIDCapture, BillPeriodDate, Airline1, Flight1, ETA1, Airline2, Flight2, PortA2, FinalDest, ETA2, ETD1, ETD2, AirlineAcctNo, 
                        DeadlineTime
FROM          dbo.Jobs
WHERE      (Closed = 0) AND (PickupDate = CONVERT(char(11), GETDATE(), 106)) OR
                        (Closed = 0) AND (PickupDate = CONVERT(char(11), GETDATE() - 1, 106))

ORDER BY CASE WHEN CourierID = 0 THEN 0 ELSE 1 END, WaybillNumber DESC

Open in new window

0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39687868
SELECT      TOP 999999 WaybillNumber, CourierID, SendersAcctNo, CourierID2, OrderDate, OrderTime, PickupDate, PickupArrival, PickupTime, DeliveryDate,
                        DeliveryTime, CompanyName, City, CompanyName2, City2, NoOfPieces, Weight, BillingRef, RecipientsSignature, ReqPickupTime, Cancel, NightRate,
                        WERate, HolRate, Dist, PickupLate, State, State2, PortA1, AirbillNo, Street, Street2, ReqPickupDate, Miles, DriverAmount, Dept, UserID, Zip, Zip2,
                        EnableRte, DeliveryArrival, CheckMileage, Closed, GetCharges, ChargesGiven, Posted, Invoiced, Returnz, AddressType1, AddressType2, [Full],
                        VehicleType, CardTransIDCapture, BillPeriodDate, Airline1, Flight1, ETA1, Airline2, Flight2, PortA2, FinalDest, ETA2, ETD1, ETD2, AirlineAcctNo,
                        DeadlineTime,
                                    (CASE WHEN CourierID = 0 THEN 0 ELSE 1 END) AS Sort1,
                                    (CASE WHEN CourierID=0 THEN ISNULL(ReqPickupTime,  '1000-01-01') ELSE ISNULL(DeadlineTime, '1000-01-01') END) AS Sort2
                                     INTO #tempTable
FROM          dbo.Jobs
WHERE      (Closed = 0) AND (PickupDate = CONVERT(char(11), GETDATE(), 106)) OR
                        (Closed = 0) AND (PickupDate = CONVERT(char(11), GETDATE() - 1, 106))


SELECT * FROM #tempTable
ORDER BY Sort1, WaybillNumber DESC, Sort2
0
 

Author Comment

by:Dale Massicotte
ID: 39688558
I can't save the view in SQL MGT STUDIO 2008

doesn't seem to like INTO or #tempTable

should this be done as stored proc - do I need to create a tempTable?
0
 
LVL 8

Assisted Solution

by:Mohit Vijay
Mohit Vijay earned 100 total points
ID: 39688590
Yes, this is related to stored procedure, but before you move forward, give it a try in SQL Query Editior and see if you are getting correct data or not. Is it going to fulfil you requirement or not, just validate it first.
0
 

Author Comment

by:Dale Massicotte
ID: 39688694
where would there be a datatype error?

Msg 242, Level 16, State 3, Line 3
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

(0 row(s) affected)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 300 total points
ID: 39688770
>>where would there be a datatype error?
in your where clause you are comparing dates or datetimes to varchar


There a few points I'd like to raise.

1. using an order by clause in a view is not good practice
2. converting getdate to varchar using style 106 isn't a good way to filter date/time information
3. the parentheses used in the where clause might cause unexpected results

I suggest this as the where clause (this assumes the field PickupDate stored a date only or that it stores dates with time at 00:00:00):
WHERE Closed = 0
      AND (
            PickupDate = dateadd(dd, datediff(dd,0, getDate()), -1)
          OR
            PickupDate = dateadd(dd, datediff(dd,0, getDate()), 0)
          )

Open in new window

I would also suggest removing the "TOP 999999" and the order by clause from the view, however you might want to include a calculated column that will simplify sorting when you use the view's results.

It would help if you could answer jimhorn's question about the data types of ReqPickupTime and DeadlineTime. Are they "time"?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now