Get Date Ranges in SQL

Question

I have a table that look something like follows

Price   | DateTime
--------+-------------
100     | 22/01/2016
210     | 23/01/2016
110     | 24/01/2016
10      | 25/01/2016
20      | 26/01/2016

30      | 13/03/2016
40      | 14/03/2016
50      | 15/03/2016
60      | 16/03/2016

Now we can see there are two date ranges in it:

1. 22/01/2016 -> 26/01/2016
2. 13/03/2016 -> 16/032016

How can I query my database to get the above results, i.e the data ranges that I have described? The data can be ordered by date but how to get the range

Answer 1

Your ranges appear to be defined by consecutive dates. You can assign the groups by subtracting an increasing number -- via row_number(). The rest is aggregation:

select min(datetime) as range_start, max(datetime) as range_end
from (select t.*,
             dateadd(day, - row_number() over (order by datetime), datetime) as grp
      from t
     ) t
group by grp;