Effective dates, complex requirements I have a table with, say, preferred colors.
For every person, we store the favorite color and the effective date. If the person changes preference, we store the new one with a newer effective date.
Example:

name   color   eff_date
John   Green   2014-03-07
Luis   Red     2014-03-07
Luis   Yellow  2015-05-02
Nina   Blue    2015-07-06


If we want to take the current preferred color of Luis:

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date=( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' )


Now we decide that persons can change their favorite color anytime, but the change will only be effective from 1st January. So even if Luis entered a record that says that his favorite color is Yellow, the query must return the old value (Red) until 1st January 2016.

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date=( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' and eff_date &lt; '1 jan 2015' )


This query seems ok, but will not return any record for Nina. The rules say that a newcomer can choose the favorite color immediately, without waiting for next 1st January.
In other words, "if possible, take a record from 1st January or earlier; if not possible, any record; take the most recent".
This is what I was able to write:

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date= ISNULL(
       ( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' and eff_date &lt; '1 jan 2015' ),
       ( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' )
       )


Is there a more elegant, or more efficient, way to achieve this result?

I have no control on the tables structure, they are from PeopleSoft.

If Nina made two selections this year I want to show the most recent one.
I explain how to do this here: http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/

The basic concept is to use OUTER APPLY with TOP (1). In your case you may need to start with your list of people that you're looking up. Like this:

SELECT p.Person, ISNULL(c1.color, c2.color) AS color
FROM (VALUES ('Nina')) AS p(Person)
OUTER APPLY 
    (SELECT TOP (1) f.color
    FROM fav_colors AS f
    WHERE f.name = p.Person
    AND f.eff_date &lt;= @date
    ORDER BY f.eff_date DESC) c1
OUTER APPLY
   (SELECT TOP (1) f.color
    FROM fav_colors AS f
    WHERE f.name = p.Person
    AND c1.color IS NULL
    ORDER BY f.eff_date ASC) c2


The post details how the Query Optimizer will only do the second lookup if the first one fails. It's really very efficient with the right index in place (on (name, eff_date) include (color)).

Oh - if color can be NULL legitimately, then test a different column in the c2 sub-query.