Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > SQL SERVER (TECHNET) > Conditional Order By statement
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 25th July 09:18
wezgv&r
External User
 
Posts: 1
Default Conditional Order By statement


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.
  Reply With Quote


  sponsored links


2 25th July 09:18
hugo kornelis
External User
 
Posts: 1
Default Conditional Order By statement


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
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666