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