Convert data rows to date ranges

Question

I want convert data rows to date ranges in sql. below are sample data

#Slno   MonthDate   System#
1   7/1/2017    SystemA
1   8/1/2017    SystemA
1   9/1/2017    SystemB
1   10/1/2017   SystemB
1   11/1/2017   SystemB
1   12/1/2017   SystemA
1   1/1/2018    SystemA
1   2/1/2018    SystemA
1   3/1/2018    SystemB
2   12/1/2017   SystemA
2   1/1/2018    SystemB
3   2/1/2018    SystemA
4   3/1/2018    SystemB

and expected output is

#Slno   StartDate   EndDate System#
1   7/1/2017    8/1/2017    SystemA
1   9/1/2017    11/1/2017   SystemB
1   12/1/2017   3/1/2018    SystemA
2   12/1/2017   12/1/2017   SystemA
2   1/1/2018    1/1/2018    SystemB
3   2/1/2018    2/1/2018    SystemA
4   3/1/2018    3/1/2018    SystemB

Answer 1

You can do this using the "difference of row numbers" method:

select slno, system, min(date), max(date)
from (select t.*,
             row_number() over (partition by slno order by date) as seqnum_s,
             row_number() over (partition by slno, system order by date) as seqnum_ss
      from t
     ) t
group by slno, system, (seqnum_s - seqnum_ss);

The logic for this is a bit tricky. In my experience, you can run the subquery and stare at the results. You should be able to see how the difference of the row numbers defines the groups defined by equal values in adjacent rows.

Answer 2

Here is another approach using window functions

;WITH cte
     AS (SELECT *,
                Grp = Sum(CASE WHEN System = prev_System THEN 0 ELSE 1 END)
                        OVER(partition BY Slno ORDER BY MonthDate)
         FROM   (SELECT *,
                        prev_System = Lag(System)OVER(partition BY Slno ORDER BY MonthDate)
                 FROM   Yourtable) a)
SELECT Slno, System, Min(MonthDate), Max(MonthDate)
FROM   cte
GROUP  BY Slno, System, Grp 

Lag(System)OVER(partition BY Slno ORDER BY MonthDate) helps you to identify the previous sytem for each monthdate

Sum(CASE WHEN System = prev_System THEN 0 ELSE 1 END) OVER(partition BY Slno ORDER BY MonthDate) helps you to create group when the previous and current system is same

Note : This works from Sql Server 2012