2

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 < '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 < '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.

0
3

You can simplify the first and second query with TOP / ORDER BY:

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

It won't however return anything for Nina. You could use another subquery and then combine them with either UNION ALL and one more TOP or with COALESCE() (or ISNULL() as in your query):

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
    ) ) ;

If you want to do this check for more than one (or all of the) persons on the table, you can use OUTER APPLY, as @RobFarley's explains in his answer.)

0
4

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

0

I think you need to change the data type of the eff_date column to DATETIME to avoid problems with people making two or more selections in the same day.

The PostYearStart_MAX_eff_date column in the CTE should perhaps be changed to MIN depending on your response to my question in the comments.

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
0
0

Your problem comes from the fact that you are mixing /use same column for two types of information:

  • Choice made / data entry date
  • Effective date - when to apply the choice from

To make your data model more elegant and give more freedom consider adding "Updated date" column. Then you can populate it with the information from "Effective Date" column you are using now. At the same time present "Effective Date" column will contain real Effective Date, e.g. 1 Jan 2015.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.