user3691566 user3691566 - 1 year ago 132
SQL Question

SQL Ranking by consecutive date blocks

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download