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

​x
 
Date        Severity2018-09-10  402018-09-11  402018-09-12  402018-09-13  402018-09-14  402018-09-15  402018-09-16  202018-09-17  202018-09-18  202018-09-19  302018-09-20  302018-09-21  302018-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

 
Start      End        Severity2018-09-10 2018-09-15 402018-09-16 2018-09-18 202018-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;

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

This will get adjacent rows with the same severity.

Advertisement

Answer