Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORDER BY CASE END QUESTIONS - SORT QUESTION

Posted on 2013-11-30
15
Medium Priority
?
246 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 66

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 66

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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 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 400 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 49

Accepted Solution

by:
PortletPaul earned 1200 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

963 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