Is it possible to select a specific ORDER BY in SQL Server 2008?

Question

I have a table that holds days and times, the day column, can have any of the seven days entered into it, and they are set to data type varchar. As this table holds booking times for a client, I want to select all days from the table where the id matches, and I want to sort by day Monday-Sunday. I was hoping that I could add something to this query to manually select the order the results come back like so:

select * 
from requirements 
where Family_ID = 1 
ORDER BY Day, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

This of course doesn't work but I just wanted to show what I am trying to achieve. The client doesn't necessarily require help every day, I just want to show the days they are booked in.

Sorting by DESC and ASC doesn't help with days of the week, I would appreciate any tips on how to achieve this.

Thanks.

Answer 1

Hmm.. that's nasty, the days are stored as verbatim 'Monday', 'Tuesday', etc?

Anyway, just do this:

SELECT * 
FROM Requirements
ORDER BY 
     CASE Day 
     WHEN 'Monday' THEN 1
     WHEN 'Tuesday' THEN 2
     WHEN 'Wednesday' THEN 3
     WHEN 'Thursday' THEN 4
     WHEN 'Friday' THEN 5
     WHEN 'Saturday' THEN 6
     WHEN 'Sunday' THEN 7
     END