CODEWITHSUNDEEP
sqlsql-servergaps-and-islands
Prashant Kumar
Prashant Kumar

Reputation: 22539

Group rows by contiguous date ranges for groups of values

Consider some table T, ordered by Col1, Col2, Date1, Date2:

Col1    Col2    Date1         Date2          rate
ABC     123     11/4/2014     11/5/2014      -90
ABC     123     11/4/2014     11/6/2014      -55
ABC     123     11/4/2014     11/7/2014      -90
ABC     123     11/4/2014     11/10/2014     -90

I want to group the data so that changes are easily audited/reduce repetition, so I have

Col1    Col2    Date1         start_Date2    end_Date2      rate
ABC     123     11/4/2014     11/5/2014      11/5/2014      -90
ABC     123     11/4/2014     11/6/2014      11/6/2014      -55
ABC     123     11/4/2014     11/7/2014      11/10/2014     -90

I can easily do that if I can get another column with the rows numbered as 1 2 3 3 (only important that numbers are distinct), and then GROUP BY that column.

My attempt at the query:

SELECT *, DENSE_RANK() OVER (ORDER BY rate) island
FROM T
ORDER BY Date2

doesn't give what I'm looking for:

Col1    Col2    Date1         Date2          rate     island
ABC     123     11/4/2014     11/5/2014      -90      1
ABC     123     11/4/2014     11/6/2014      -55      2
ABC     123     11/4/2014     11/7/2014      -90      1
ABC     123     11/4/2014     11/10/2014     -90      1

I want the query to recognize the second group of -90 values should be treated as a new group, since they appeared after a group with a different rate.

The [gaps-and-islands] SQL tag was pretty helpful, but I'm not quite able to figure out how to handle when the rate reverts back to a previous value. How should I modify my query?

Upvotes: 3

Views: 1496

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can identify the groups by using the difference of row_numbers(). Consecutive values will have a constant.

select col1, col2, date1, min(date2), max(date2), rate
from (select t.*,
             (row_number() over (partition by col1, col2, date1 order by date2) -
              row_number() over (partition by col1, col2, date1, rate order by date2)
             ) as grp
      from table t
     ) t
group by col1, col2, date1, rate, grp

Upvotes: 3

Related Questions