Ranking items according to date and amount in SQL-CodePudding

I have the following lines for the same item:

ID  START_DATE  END_DATE    AMOUNT  RANK    MIN START DATE
1   11/04/2012  24/10/2012  6   2   11/04/2012
1   25/10/2012  09/12/2012  9   1   25/10/2012
1   10/12/2012  10/04/2013  9   1   25/10/2012
1   11/04/2013  17/06/2014  9   1   25/10/2012
1   15/09/2019  10/10/2019  6   2   11/04/2012

what i want is to get the min start date according to each item, the problem is that I have the amount 6 on two different periods which confuses my ranking. What I expect to have is

ID  START_DATE  END_DATE    AMOUNT  RANK    MIN START DATE
1   11/04/2012  24/10/2012  6   2   11/04/2012
1   25/10/2012  09/12/2012  9   1   25/10/2012
1   10/12/2012  10/04/2013  9   1   25/10/2012
1   11/04/2013  17/06/2014  9   1   25/10/2012
1   15/09/2019  10/10/2019  6   3   15/09/2019

Any tips ? Thanks

CodePudding user response：

UNTESTED:

With CTE AS (
SELECT ID, Start_Date, End_Date, Amount
     , Rank, min(start_Date) over (Partition by ID, Amount) as Min_Start_Date
FROM TableName)

SELECT ID, STart_Date,End_Date, Amount
     , Rank() over (Partition by ID order by Min_Start_Date) as Rank
    -- Might need  
     --, Rank(Min_Start_Date) over (Partition by ID order by Min_Start_Date) as Rank
     , Min_Start_Date
FROM CTE
ORDER BY by Start_Date

CodePudding user response：

This is a gaps and islands problem, it can be solved using row_number function to define groups for the consecutive amount values as the following:

SELECT ID, START_DATE, END_DATE, AMOUNT,
       DENSE_RANK() OVER (ORDER BY GRP) AS RANK,
       MIN(START_DATE) OVER (PARTITION BY GRP) AS MIN_START_DATE
FROM
  (
    SELECT ID, START_DATE, END_DATE, AMOUNT,
       ROW_NUMBER() OVER (PARTITION BY ID ORDER BY START_DATE) -
       ROW_NUMBER() OVER (PARTITION BY ID, AMOUNT ORDER BY START_DATE) GRP
    FROM table_name
  ) T
 ORDER BY START_DATE

The output of this query:

ID  START_DATE  END_DATE    AMOUNT  RANK    MIN_START_DATE
1   2012-04-11  2012-10-24  6       1       2012-04-11
1   2012-10-25  2012-12-09  9       2       2012-10-25
1   2012-12-10  2013-04-10  9       2       2012-10-25
1   2013-04-11  2014-06-17  9       2       2012-10-25
1   2019-09-15  2019-10-10  6       3       2019-09-15

See a demo.