Datas efetivas, requisitos complexos

Eu explico como fazer isso aqui: http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/

O conceito básico é usar OUTER APPLY com TOP (1). No seu caso, pode ser necessário começar com a lista de pessoas que você está procurando. Assim:

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 <= @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

A postagem detalha como o Query Optimizer só fará a segunda pesquisa se a primeira falhar. É realmente muito eficiente com o índice correto no lugar (em (name, eff_date) include (color)).

Oh - se a cor puder ser NULL legitimamente, teste uma coluna diferente na subconsulta c2.

Você pode simplificar a primeira e a segunda consulta com TOP / ORDER BY:

SELECT TOP (1) color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date < '2015-01-01'
ORDER BY eff_date DESC ;

No entanto, não retornará nada para Nina. Você pode usar outra subconsulta e combiná-las com UNION ALLmais uma TOPou com COALESCE()(ou ISNULL()como na sua consulta):

SELECT color = COALESCE(
    ( SELECT TOP (1) color
        FROM fav_colors
       WHERE name='Luis'
         AND eff_date < '2015-01-01'
      ORDER BY eff_date DESC
    ),
    ( SELECT TOP (1) color
        FROM fav_colors
       WHERE name='Luis'
         AND eff_date >= '2015-01-01'
      ORDER BY eff_date ASC
    ) ) ;

Se você quiser fazer esta verificação para mais de uma (ou todas as) pessoas na mesa, você pode usar OUTER APPLY, como @RobFarley's explica em sua resposta .)

Acho que você precisa alterar o tipo de dados da eff_datecoluna DATETIMEpara evitar problemas com pessoas fazendo duas ou mais seleções no mesmo dia.

A PostYearStart_MAX_eff_datecoluna no CTEtalvez deva ser alterada MINdependendo da sua resposta à minha pergunta nos comentários.

DECLARE @YearStart DATE = '2015-01-01', @Name NVARCHAR(50) = 'nina'

DECLARE @fav_colors TABLE
(
    name    NVARCHAR(50)
    ,color  NVARCHAR(25)
    ,eff_date   DATETIME -- DATETIME needed to differentitate between two or more selections made on the same day
)

INSERt INTO @fav_colors
SELECT 'John', 'Green', '2014-03-07'
UNION
SELECT  'Luis', 'Red', '2014-03-07'
UNION 
SELECT  'Luis', 'Yellow', '2015-05-02'
UNION
SELECT  'Nina', 'Blue', '2015-07-06';


WITH CTE_Colors
AS
(
    SELECT  name
            ,COUNT(1) AS SelectionCount
            ,MAX(eff_date) MAX_eff_date
            ,SUM(CASE WHEN eff_date < @YearStart THEN 1 ELSE 0 END) AS PreYearStartSelectionCount 
            ,MAX(CASE WHEN eff_date < @YearStart THEN eff_date ELSE '1900-01-01' END) AS PreYearStart_MAX_eff_date
            ,SUM(CASE WHEN eff_date >= @YearStart THEN 1 ELSE 0 END) AS PostYearStartSelectionCount 
            ,MAX(CASE WHEN eff_date >= @YearStart THEN eff_date ELSE '1900-01-01'  END) AS PostYearStart_MAX_eff_date

    FROM    @fav_colors

    WHERE   name = @Name

    GROUP BY name
)

SELECT      fc.color

FROM        @fav_colors fc
LEFT JOIN   CTE_Colors cte ON fc.name = cte.name

WHERE       CASE 
                WHEN SelectionCount = 1
                THEN MAX_eff_date
                WHEN PreYearStartSelectionCount > 0 
                THEN PreYearStart_MAX_eff_date
                WHEN PostYearStartSelectionCount > 0    
                THEN PostYearStart_MAX_eff_date                 
            END = fc.eff_date

Seu problema vem do fato de você estar misturando/usando a mesma coluna para dois tipos de informação:

• Escolha feita / data de entrada de dados
• Data efetiva - quando aplicar a escolha de

Para tornar seu modelo de dados mais elegante e dar mais liberdade, considere adicionar a coluna "Data atualizada". Em seguida, você pode preenchê-lo com as informações da coluna "Data efetiva" que está usando agora. Ao mesmo tempo, a coluna "Data Efetiva" presente conterá a Data Efetiva real, por exemplo, 1º de janeiro de 2015.