wezgv&r

I have a table that stores three columns of data, namely

1. Order_ID
2. Exam_Start_Date
3. Order_Received_Date

I want to order the records as follows:
If the Exam_Start_Date is within the next 10 days or past, then order by the
exam_start_date. Otherwise order by the order_Received_Date. Therefore the
result set should display records where the exam_start_date is within next 10
days first, then display all other records.

I have tried the following SQL but it doesnt appear to work (i.e. Order of
the records is not exam_start_date (if within next 10 days), otherwise
Order_Received_Date.

select order_id, order_received_date, exam_start_date
from orders
ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
Order_Received_Date END

Any suggestions greatly appreciated!

Wes.

hugo kornelis

Hi Wes,

Try if this works better:

ORDER BY CASE WHEN Exam_Start_date < '20050118'
THEN Exam_Start_Date
ELSE '20050118'
END DESC,
Order_Received_Date DESC

Or, a more generic version:

ORDER BY CASE WHEN Exam_Start_date < DATEADD(day,-10, CURRENT_TIMESTAMP)
THEN Exam_Start_Date
ELSE DATEADD(day,-10, CURRENT_TIMESTAMP)
END DESC,
Order_Received_Date DESC


--
Hugo Kornelis, SQL Server MVP