I have a result set of continuous days and a severity rating. I would like to aggregate these into StartDate, EndDate, Severity

In SQL Server I have a table with the following data

Date        Severity
2018-09-10  40
2018-09-11  40
2018-09-12  40
2018-09-13  40
2018-09-14  40
2018-09-15  40
2018-09-16  20
2018-09-17  20
2018-09-18  20
2018-09-19  30
2018-09-20  30
2018-09-21  30
2018-09-22  30

i would like to obtain an aggregate of the dates and the severity

Start      End        Severity
2018-09-10 2018-09-15 40
2018-09-16 2018-09-18 20
2018-09-19 2018-09-22 30

Answer

I am guessing this is really a gaps-and-islands problem. If so:

select severity, min(date), max(date)
from (select t.*,
             row_number() over (partition by severity order by date) as seqnum
      from t
     ) t
group by dateadd(day, - seqnum, date), severity;

This will get adjacent rows with the same severity.