Case to sort on columns whose value is conditional...

Recently, I ran across a situation where I wanted to sort by a "status" of an assignment. The problem was, the assignment status was not a value held in the database, but a value determined by a set of conditions, which are as follows:

• An assignment is "completed" if a document has been turned in (sets DateTimeCompleted to a date time value instead of null). This is regardless of wheter the startdate and the duedate are current, or past.
  
  
• An assignment is "current" only if the current date is between the start and due dates, AND the completed assignment hasn't been submitted (which would set DateTimeCompleted to a non-null value).
  
  
• An assignment is "past due" when the due date has passed, and DateTimeCompleted IS NULL.
  
  
• An assignment is "upcoming" if the start date has not yet passed.
  

I wanted a column in my query called "status" which held one of the above 4 values.

I also wanted a column called "statusSort" which would be a numeric value I could sort on for display purposes.

Here's the code that got me those two columns:

Select 
CASE 
  WHEN a.DueDate < #Now()# AND c.DateTimeCompleted IS NULL THEN 'Past Due'
  WHEN a.StartDate < #Now()# and a.DueDate > #Now()# 
    AND c.DateTimeCompleted IS NULL THEN 'Current'
  WHEN a.StartDate > #Now()# THEN 'Upcoming' 
  ELSE 'Completed' 
END AS Status,

CASE 
  WHEN a.DueDate < #Now()# AND c.DateTimeCompleted IS NULL THEN 1
  WHEN a.StartDate < #Now()# and a.DueDate > #Now()# 
    AND c.DateTimeCompleted IS NULL THEN 2
  WHEN a.StartDate > #Now()# THEN 3 
  ELSE 4 
END AS StatusSort

FROM yada yada...
ORDER BY StatusSort

A Great page that explains this can be found at http://www.redhat.com/docs/manuals/database/RHDB-7.1.3-Manual/sql/queries.html
- You'll have to scroll most of the way down and look for "Case"

Here's the excerpt from the page in case it evaporates in the future:

CASE

CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other languages. CASE clauses can be used wherever an expression is valid. condition is an expression that returns a boolean result. If the result is true then the value of the CASE expression is result. If the result is false any subsequent WHEN clauses are searched in the same manner. If no WHEN condition is true then the value of the case expression is the result in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is NULL.

An example:

=> SELECT * FROM test;
a
---
1
2
3

=> SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two'
ELSE 'other' END FROM test;
a | case
---+-------
1 | one
2 | two
3 | other

The data types of all the result expressions must be coercible to a single output type. See the Section called UNION and CASE Constructs in Chapter 2 for more detail.

CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END

This CASE expression (referred to in the SQL Standard as the "simple case") is a specialized variant of the general form above. The expression is computed and compared to all the values in the WHEN clauses until one is found that is equal. If no match is found, the result in the ELSE clause (or NULL) is returned. This is similar to the switch statement in C.

The example above can be written using the simple CASE syntax:

=> SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two'
ELSE 'other' END FROM test;
a | case
---+-------
1 | one
2 | two
3 | other