Wezgv&r 2006-07-25 09:18:29
I have a table that stores three columns of data, namely
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
select order_id, order_received_date, exam_start_date
ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE Order_Received_Date END Any suggestions greatly appreciated! Wes.
Hugo kornelis 2006-07-25 09:18:45
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