user3691566 - 2 months ago 13x

SQL Question

I'm trying to rank the number of consecutive date blocks but what is the best way to do this? Example below shows the first 3 blocks being consecutive and then the 4 has a month between them so the counting would begin again.

Data I'm trying to order:

`StartDate | EndDate |Rank`

----------+-----------+----

01/01/2016| 01/02/2016| 1

01/02/2016| 01/03/2016| 2

01/03/2016| 01/04/2016| 3

01/05/2016| 01/06/2016| 1

Answer

You can do this by identifying where a grouping begins, doing a cumulative sum to identify the group, and then a row number:

```
select t.*,
row_number() over (partition by grp order by startdate) as rank
from (select t.*,
sum(case when tprev.startdate is null then 1 else 0 end) over (order by startdate) as grp
from t left join
t tprev
on t.startdate = tprev.enddate
) t;
```

This particular SQL works for the data you have presented. It will not handle data that overlaps by more than one day, nor multiple records that start on the same day. These can be handled. If your data is more like that, then ask *another* question with appropriate data in it.

Source (Stackoverflow)

Comments