SQL - Complex RANK

Question

Here is a little mind-breaker:

Platform is MS SQL 2008, but the problem is general.

I have a table table with 3 columns: CLIENT,DATE,DESTINATION_PREFERENCE

TABLE1
-------------------------------------------------------
CLIENT      |DATE       |DESTINATION_PREFERENCE
-------------------------------------------------------
Akme        |2014-01    |1
Akme        |2014-02    |6
Akme        |2014-02    |3
Akme        |2014-03    |5
Yutani      |2014-01    |5
Yutani      |2014-02    |8
Yutani      |2014-03    |3
Yutani      |2014-03    |5

What I have to do is actually 2 things at once:

1st is very simple, and a classic problem:

Selecting the rows with the one minimum DESTINATION_PREFERENCE from each group of CLIENT, and DATE.

In other words, we GROUP BY on CLIENT,DATE, then we choose the rows with the lowest DESTINATION_PREFERENCE.

Note: I use only YEAR and MONTH from the DATE.

This can be easily solved by RANK:

    SELECT 
    CLIENT,DATE,DESTINATION_PREFERENCE 
    FROM
    (
        SELECT 
        CLIENT,DATE,DESTINATION_PREFERENCE,
        RANK() OVER (PARTITION BY CLIENT, DATE ORDER BY DESTINATION_PREFERENCE ASC) AS RANKING
        FROM
        #table1
    ) sq
    WHERE
    RANKING = 1 

Good result is, where we choose rows only with the lowest DESTINATION_PREFERENCE from each group of CLIENT and DATE(YEAR,MONTH):

    CLIENT  DATE    DESTINATION_PREFERENCE
    Akma    2014-01 1
    Akma    2014-02 3
    Akma    2014-03 5
    Yutani  2014-01 5
    Yutani  2014-02 8
    Yutani  2014-03 3

2nd - Now comes the hard part. Which I cannot solve, and need some advice:

If the DESTINATION_PREFERENCE is 3 I should still include rows where DESTINATION_PREFERENCE equals to 6.

So the result table value will have one extra row (2nd row):

CLIENT  DATE    DESTINATION_PREFERENCE
Akma    2014-01 1
Akme    2014-02 6
Akma    2014-02 3
Akma    2014-03 5
Yutani  2014-01 5
Yutani  2014-02 8
Yutani  2014-03 3   

How could I extend RANK() to such include arbitrary rules like that? Example rules to implement:

If the top lowest value of DESTINATION_PREFERENCE is 3 in the group, then include rows with values of 6 as well from the same group.

If the top lowest value of DESTINATION_PREFERENCE is 9 in the group, then include rows with values of 2 as well from the same group.

If the top lowest value of DESTINATION_PREFERENCE is 128 in the group then include rows with values of 312 as well from the same group.

etc ...

There are many rules.

Thank you for the tips in advance!

Answer 1

You can use CTE with adding additional column, where you simply replace 6 with 3, 9 with 2 etc.

DECLARE @t TABLE
    (
      client NVARCHAR(MAX) ,
      date DATETIME ,
      dest INT
    )

INSERT  INTO @t
VALUES  ( 'Akme', '20140101', 1 ),
        ( 'Akme', '20140102', 3 ),
        ( 'Akme', '20140102', 6 ),
        ( 'Akme', '20140103', 5 ),
        ( 'Yutani', '20140104', 2 ),
        ( 'Yutani', '20140104', 7 ),
        ( 'Yutani', '20140104', 9 ),
        ( 'Yutani', '20140105', 7 );
WITH    cte
          AS ( SELECT   client ,
                        date ,
                        dest ,
                        CASE dest
                          WHEN 6 THEN 3
                          WHEN 9 THEN 2
                          ELSE dest
                        END AS rndest
               FROM     @t
             )
    SELECT  CLIENT ,
            DATE ,
            dest
    FROM    ( SELECT    CLIENT ,
                        DATE ,
                        dest ,
                        RANK() OVER ( PARTITION BY CLIENT, DATE ORDER BY rndest ASC ) AS RANKING
              FROM      cte
            ) sq
    WHERE   RANKING = 1 

Output:

CLIENT  DATE    dest
Akme    2014-01-01 00:00:00.000 1
Akme    2014-01-02 00:00:00.000 3
Akme    2014-01-02 00:00:00.000 6
Akme    2014-01-03 00:00:00.000 5
Yutani  2014-01-04 00:00:00.000 2
Yutani  2014-01-04 00:00:00.000 9
Yutani  2014-01-05 00:00:00.000 7