Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

ORDER BY CASE END QUESTIONS - SORT QUESTION

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
Dale Massicotte
Asked:
Dale Massicotte
  • 6
  • 5
  • 3
  • +1
3 Solutions
 
Mohit VijayCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Dale MassicottePresidentAuthor Commented:
Thanks jim for helping me write the query but SQL Server cannot parse ERROR
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>SQL Server cannot parse ERROR
You'll have to spell out exactly what you mean by this.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The above also requires that ReqPickupTime and DeadlineTime are of the same data type.
So ... what data type are they?
0
 
Mohit VijayCommented:
Best and clean solution, As I said earlier, split the query, you will get fast result and readable query.
0
 
Dale MassicottePresidentAuthor Commented:
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
 
Dale MassicottePresidentAuthor Commented:
VjSoft isn't that what JimHorn is trying to show me while you keep saying the same thing and offering nothing?
0
 
Mohit VijayCommented:
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
 
Dale MassicottePresidentAuthor Commented:
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
 
Mohit VijayCommented:
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
 
Dale MassicottePresidentAuthor Commented:
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
 
Mohit VijayCommented:
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
 
Dale MassicottePresidentAuthor Commented:
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
 
PortletPaulCommented:
>>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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now