Solved

ORDER BY CASE END QUESTIONS - SORT QUESTION

Posted on 2013-11-30
15
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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