May 7, 2011 at 6:15 pm
I'm in a situation where I can't use a typical ORDER BY statement to sort records. Each record has three date columns - Booking Date, Deposit Date and Arrival Date. Normally, a statement such as ORDER BY BookingDate, DepositDate, ArrivalDate would work, however the only date that is guaranteed to exist it ArrivalDate AND the reservations need to be sorted by Arrival date first and then Booking and Deposit if they exist.
ORDER BY BookingDate, DepositDate, ArrivalDate
Booking Deposit Arrival
5/9
5/10
5/1 5/1 5/3
5/1 5/1 5/4
5/1 5/1 5/9
Custom Sort
Booking Deposit Arrival
5/1 5/1 5/3
5/1 5/1 5/4
5/9
5/1 5/1 5/9
5/10
My solution is to create a custom sort column via a function that takes the Booking, Deposit and Arrival Dates and then builds a value that the records can be sorted on. The value would be calculated by taking the 100yr value of the dates and concatenating them into a string. Doing something like this...
SELECT CAST(CAST(CAST('1/31/1979' as datetime) as int) as varchar) + CAST(CAST(CAST('1/31/1989' as datetime) as int) as varchar) + CAST(CAST(CAST('1/31/1999' as datetime) as int) as varchar)
The actual function will check to see if the Booking Date and/or Deposit Date are missing if so, use the Arrival Date. The calculated value in that situation would be [Arrival Date][Arrival Date][Arrival Date]. Using this in the ORDER BY statement should get the records in the sort order that I want.
The question: What is the best/easiest/most efficient way to convert a date value to a string?
May 7, 2011 at 6:45 pm
i would order by datetime, and never convert.
you could use the COALESCE function to get the order you want i think; that would be the easiest:
ORDER BY COALESCE(ArrivalDate,BookingDate,DepositDate)
Lowell
May 8, 2011 at 2:55 pm
Lowell (5/7/2011)
i would order by datetime, and never convert.you could use the COALESCE function to get the order you want i think; that would be the easiest:
ORDER BY COALESCE(ArrivalDate,BookingDate,DepositDate)
Since ArrivalDate is apparently the one guaranteed to be there, that amounts to sorting on arrival date; maybe what Lowell meant was
ORDER BY ArrivalDate, Coalesce(BookingDate, ArrivalDate), Coalesce(DepositDate, BookingDate, ArrivalDate) ? But if I read the question right if doing anything like that it should be
ORDER BY ArrivalDate, Coalesce(BookingDate, ArrivalDate), Coalesce(DepositDate, ArrivalDate)
since BookingDate and DepositDate are present or absent together?
Anyway, you asked for a conversion to string method; so here goes:-
The easiest way to convert ArrivalDate to a string while preserving the date sort order is
Convert(char(8),ArrivalDate,112) if only the date matters, or
Convert(char(23),ArrivalDate,126) if the time matters as well as the date.
For the items that could be NULL, coalesce is still needed: for example for BookingDate, it would be
Convert(char(8),Coalesce(BookingDate,ArrivalDate),112) or Convert(char(23),Coalesce(BookingDate,ArrivalDate),126)
and for DepositDate
Convert(char(8),Coalesce(DepositDate,ArrivalDate), 112) or
Convert(char(23),Coalesce(DepositDate,ArrivalDate),126)
depending on whether the time was relevant or not.
(I imagine time is irrelevant; if so char(8) and conversion type 112 are correct.)
But I prefer the approach without convert, though, as Lowell suggested. It's simpler, less code, probably faster. Why convert when it's completely unnecessary?
Tom
June 14, 2011 at 12:43 pm
Tom.Thomson (5/8/2011)
Lowell (5/7/2011)
i would order by datetime, and never convert.you could use the COALESCE function to get the order you want i think; that would be the easiest:
ORDER BY COALESCE(ArrivalDate,BookingDate,DepositDate)
Since ArrivalDate is apparently the one guaranteed to be there, that amounts to sorting on arrival date; maybe what Lowell meant was
ORDER BY ArrivalDate, Coalesce(BookingDate, ArrivalDate), Coalesce(DepositDate, BookingDate, ArrivalDate) ? But if I read the question right if doing anything like that it should be
ORDER BY ArrivalDate, Coalesce(BookingDate, ArrivalDate), Coalesce(DepositDate, ArrivalDate)
since BookingDate and DepositDate are present or absent together?
Anyway, you asked for a conversion to string method; so here goes:-
The easiest way to convert ArrivalDate to a string while preserving the date sort order is
Convert(char(8),ArrivalDate,112) if only the date matters, or
Convert(char(23),ArrivalDate,126) if the time matters as well as the date.
For the items that could be NULL, coalesce is still needed: for example for BookingDate, it would be
Convert(char(8),Coalesce(BookingDate,ArrivalDate),112) or Convert(char(23),Coalesce(BookingDate,ArrivalDate),126)
and for DepositDate
Convert(char(8),Coalesce(DepositDate,ArrivalDate), 112) or
Convert(char(23),Coalesce(DepositDate,ArrivalDate),126)
depending on whether the time was relevant or not.
(I imagine time is irrelevant; if so char(8) and conversion type 112 are correct.)
But I prefer the approach without convert, though, as Lowell suggested. It's simpler, less code, probably faster. Why convert when it's completely unnecessary?
COALESCE picks the first non-NULL value, so to solve the OP's question, he needs only use the date in a date format with the COALESCE as Lowell said. He just had the columns in the wrong order for what the OP was asking. Should have been:
ORDER BY COALESCE(BookingDate,DepositDate,ArrivalDate)
That will sort by BookingDate if it's present. If not, it will move on to DepositDate. If neither are present, it will sort by the ArrivalDate, which is always present.
June 14, 2011 at 1:39 pm
Matt Wright (6/14/2011)
COALESCE picks the first non-NULL value, so to solve the OP's question, he needs only use the date in a date format with the COALESCE as Lowell said. He just had the columns in the wrong order for what the OP was asking. Should have been:ORDER BY COALESCE(BookingDate,DepositDate,ArrivalDate)
That will sort by BookingDate if it's present. If not, it will move on to DepositDate. If neither are present, it will sort by the ArrivalDate, which is always present.
Unfortunately no sort on the output of a single simple three-argument coalesce will deliver the stated requirement, which was
the reservations need to be sorted by Arrival date first and then Booking and Deposit if they exist
In particular, the order you suggest for a single coalesced value is perhaps worse than Lowell's order, not better, because his at least sorts by ArrivalDate first, which is part of the requirement, while yours doesn't do that unless both the other fields are null. Or perhaps not worse, as your output would be more obviously wrong than his output so the error might be noticed sooner (further from right is sometimes better rather than worse).
As I said before, I would regard a solution without converts (as suggested by Lowell) as better than one with converts; but the sort order is required to be affected by all three attributes; that means that each of the two items which can potentially be null has to be coalesced to a "harmless value" whenever it is. Of course what the "harmless value" will be depends on business rules which have not been stated (obviously I picked one of the obvious possibilities for them in my previous note; maybe I should have made that clearer, instead of thinking that it would be obvious that I'd made an unfounded assumption in order to illustrate what an answer might look like).
Tom
June 14, 2011 at 3:23 pm
Tom.Thomson (6/14/2011)
Matt Wright (6/14/2011)
COALESCE picks the first non-NULL value, so to solve the OP's question, he needs only use the date in a date format with the COALESCE as Lowell said. He just had the columns in the wrong order for what the OP was asking. Should have been:ORDER BY COALESCE(BookingDate,DepositDate,ArrivalDate)
That will sort by BookingDate if it's present. If not, it will move on to DepositDate. If neither are present, it will sort by the ArrivalDate, which is always present.
Unfortunately no sort on the output of a single simple three-argument coalesce will deliver the stated requirement, which was
the reservations need to be sorted by Arrival date first and then Booking and Deposit if they exist
In particular, the order you suggest for a single coalesced value is perhaps worse than Lowell's order, not better, because his at least sorts by ArrivalDate first, which is part of the requirement, while yours doesn't do that unless both the other fields are null. Or perhaps not worse, as your output would be more obviously wrong than his output so the error might be noticed sooner (further from right is sometimes better rather than worse).
As I said before, I would regard a solution without converts (as suggested by Lowell) as better than one with converts; but the sort order is required to be affected by all three attributes; that means that each of the two items which can potentially be null has to be coalesced to a "harmless value" whenever it is. Of course what the "harmless value" will be depends on business rules which have not been stated (obviously I picked one of the obvious possibilities for them in my previous note; maybe I should have made that clearer, instead of thinking that it would be obvious that I'd made an unfounded assumption in order to illustrate what an answer might look like).
I don't think it's worse, but you'd have to ask the OP. I made an assumption (I know those are bad) that he wanted it sorted by the Booking Date if it had it; if not then by the Deposit Date; if no Deposit Date then the Arrival Date. I made that assumption based on his posting of what a "correct" sort was.
The actual function will check to see if the Booking Date and/or Deposit Date are missing if so, use the Arrival Date
If that assumption was incorrect, then my suggestion was incorrect as well. If my assumption was right, then my COALESCE will in fact sort based on his requirement. Try it...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply