Custom order in SQL
I'm trying to implement "custom" ordering in a clause ORDER BY
in my SQL query using a statement CASE
, but that gives me some funky orders
Here's my suggestion ORDER BY
:
Edited to reflect the update:
ORDER BY
CASE WHEN
CheckInStatus <> 'Cancelled' AND ArrivalTime is null AND GETDATE() > DATEADD(mi,30, CAST(StartDateTime AS DATETIME))THEN 1
WHEN
CheckInStatus <> 'Cancelled' AND ArrivalTime is null AND GETDATE() <= DATEADD(mi,30, CAST(StartDateTime AS DATETIME)) THEN 2
WHEN ArrivalTime is not null THEN 3
WHEN CheckInStatus='Cancelled' THEN 4
ELSE 5
END,
StartDateTime, ScanTechName
I want to place an order like this:
People who have NOT arrived (and which appointments have not been canceled) and the current time is more than 30 minutes after StartTime - these guys should be the first
People who have NOT arrived (and who are not assigned) and the current time is less than or equal to 30 minutes after StartTime - these guys appear second
Further, everyone who is registered in
Canceled appointments follow
And finally, then everything else
And everything will be ordered by StartTime and Name
The problem seems to be happening with 2 and 3. These guys seem to be confusing and I think it might have something to do with my AND, but I'm not sure how to fix it.
Below is the error I am getting in the results. I included CASE
in ORDER BY
to visually see the problem.
Edit to include results:
Arrival Time | CheckIn Status | StartDateTime | OrderStatus
----------------------------------------------------------------------------------
2014-08-15 08:00:07.123 | Arrived | 2014-08-15 07:15:00.000 | 3
----------------------------------------------------------------------------------
2014-08-15 07:47:48.643 | Arrived | 2014-08-15 07:30:00.000 | 2
So there are a few things going on
- Since my current GETDATE () is 8/28/2014 - there shouldn't be 2 statuses for the above. As it is at least or equal to 30 minutes after StartDate
- Since this is wrong on its own, it can lead to this second question - since it is labeled as 2, it should appear before 3, not after.
source to share
This part of your condition appears to be causing your problem:
CAST(StartDateTime AS DATETIME) > DATEADD(mi,30, CAST(StartDateTime AS DATETIME))
[StartDateTime] will never be better than the same date plus 30 minutes! This is the same remark for your second condition.
If that doesn't solve your problem, can you provide more information on the results you have with this query?
source to share
This is a simple logical fallacy. You are comparing "StartDateTime" with ("StartDateTime" + 30 minutes), which will always return true. I assume you want to compare with the current time, which will make it look something like this:
ORDER BY
CASE WHEN
CheckInStatus <> 'Cancelled' AND ArrivalTime is null AND getdate() > DATEADD(mi,30, CAST(StartDateTime AS DATETIME))THEN 1
WHEN
CheckInStatus <> 'Cancelled' AND ArrivalTime is null AND getdate() <= DATEADD(mi,30, CAST(StartDateTime AS DATETIME)) THEN 2
WHEN ArrivalTime is not null THEN 3
WHEN CheckInStatus='Cancelled' THEN 4
ELSE 5
END,
StartDateTime, ScanTechName
source to share