Custom Ordering in SQL

Question

I'm trying to implement a "custom" ordering in the ORDER BY clause in my SQL query using a CASE Statement but it's giving me some funky ordering

Here is my ORDER BY clause so far:

Edited to Reflect 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

What I want to do is order the query as follows:

People who have NOT arrived (and who's appointments are not cancelled) and current time is Greater than than 30 minutes past the StartTime - these guys should be first

People who have NOT arrived (and who's appointments are not cancelled) and current time is less than or equal to 30 minutes past the StartTime - these guys appear second

Next is everyone who is checked in

Followed by Cancelled Appointments

And finally then everything else

And everything will be ordered by StartTime and Name

The issue seems to occur with 2 and 3. These guys seem to get mixed together and I think it might have to do with my AND but I'm not sure how to fix it.

Below is the error I get in the results - I've included the CASE in the ORDER BY to visually see the issue

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 a couple of things happening

1. Since my current GETDATE() is 8/28/2014 - there shouldn't be a 2 Status for the above. Since it's not Less than or equal to the 30 minutes past the StartDate
2. Since that's wrong in itself it could lead to this second issue - since it's marked as 2 it should be appearing before 3 not after.

Answer 1

This part of your condition seems to be the cause of your problem:

CAST(StartDateTime AS DATETIME) > DATEADD(mi,30, CAST(StartDateTime AS DATETIME))

[StartDateTime] will never be greather than the same date plus 30min! Same remark for your second condition.

If that's not solving your problem, can you provide more info about the result your have with this query?