Solved

ORDER BY CASE END QUESTIONS - SORT QUESTION

Posted on 2013-11-30
15
238 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot Rows To Columns 10 66
SSRS Subscription jobs disabled, yet still running 4 50
sql 2014,  lock limit 5 39
sql2016-WIn10: standard,for SQL servc-account.. 51 31
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 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