Sql-server – Effective dates, complex requirements

sql serversql server 2014

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.

Best Answer

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

Related Solutions

Sql-server – Multiple Partition Keys

You really don't need partitioning for this to work efficiently, which is what I told you on your last question as well...even if you have billions of rows.

If you cluster on the BarcodeID (which I am assuming is unique) and put a nonclustered index on LoyaltyCardID it should work just fine. These are NOT complicatd queries with a lot of additional logic from the sounds of things, and simple seek operations are extremely efficient on their own.

Are you getting pressure to partition or is it just something that you have decided to do?

Sql-server – How to speed up a query that orders by a calculated field

If you don't really need zero-second actuality, you could just run your query time to time and cache the results.

If you still need to have real-time data on this (sacrificing insert performance), I would do this:

Since self-joins are not allowed in indexed views, you need to create two copies of each table:

CREATE TABLE personBrother
        (
        personId INT NOT NULL,
        brotherName INT NOT NULL
        )

CREATE TABLE personBrother2
        (
        personId INT NOT NULL,
        brotherName INT NOT NULL
        )

Create an indexed view on their join:

CREATE VIEW
        commonBrothers
WITH SCHEMABINDING
AS
        SELECT  p1.personId AS p1,
                p2.personId AS p2,
                COUNT_BIG(*) AS cnt
        FROM    dbo.personBrother p1
        JOIN    dbo.personBrother2 p2
        ON      p1.brotherName = p2.brotherName
        WHERE   p1.personId < p2.personId
        GROUP BY
                p1.personId, p2.personId

CREATE UNIQUE CLUSTERED INDEX
        ux_commonBrothers_p1_p2
ON      commonBrothers (p1, p2)

CREATE INDEX
        ix_commonBrothers_cnt
ON      commonBrothers (cnt)

Same for sisters.

You should manually maintain these tables to have same data (write a trigger, insert/update/delete both etc).

Now we can easily get pairs with the most brothers and most sisters:

SELECT  TOP 1 WITH TIES
        *
FROM    commonBrothers
ORDER BY
        cnt DESC

All we need now is to fetch a greatest sum. Unfortunately, we cannot index a join of these views (it's a pure implementation flaw, there's no theoretical limitation for this).

So we need to do the following: the top pair cannot have less brothers than the top sis pair. Same holds for the sisters. So we have this query:

SELECT  TOP 1 WITH TIES
        cb.p1, cb.p2, cb.cnt + cs.cnt AS totalCnt
FROM    commonBrothers cb
JOIN    commonSisters cs
ON      cs.p1 = cb.p1
        AND cs.p2 = cb.p2
WHERE   cs.cnt >=
        (
        SELECT  MAX(cst.cnt)
        FROM    (
                SELECT  TOP 1 WITH TIES
                        p1, p2
                FROM    commonBrothers 
                ORDER BY
                        cnt DESC
                ) cbt
        JOIN    commonSisters cst
        ON      cst.p1 = cbt.p1
                AND cst.p2 = cbt.p2
        )
        AND cb.cnt >=
        (
        SELECT  MAX(cbt.cnt)
        FROM    (
                SELECT  TOP 1 WITH TIES
                        p1, p2
                FROM    commonSisters
                ORDER BY
                        cnt DESC
                ) cst
        JOIN    commonBrothers cbt
        ON      cbt.p1 = cst.p1
                AND cbt.p2 = cst.p2
        )
ORDER BY
        totalCnt DESC

If the numbers of common brothers and sisters are correlated, this query will be very fast.

This solution has two drawbacks:

DML performance: if you insert or delete a record for a name shared by million brothers, the indexed view will get 2M inserts or delete. This is the price you pay for real-time query: the kind of data you are asking for cannot be easily indexed.
Persons with 0 brothers or 0 sisters will not be indexed. If there's a chance that top pair will not have brothers or sisters, you should amend the last query a little.

Best Answer

Related Solutions

Sql-server – Multiple Partition Keys

Sql-server – How to speed up a query that orders by a calculated field

Related Question