SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl(
  id INT,
  rating int,
  rating_from DATE,
  rating_to DATE
);

INSERT INTO tbl VALUES
  (1, 2, '2014-05-23', '2015-04-13'),
  (1, 2, '2015-04-14', '2015-06-02'),
  (1, 2, '2015-06-03', '2016-05-31'),
  (1, 2, '2016-06-01', '2018-03-22'),
  (2, 1, '2016-06-01', '9999-12-31'),
  (3, 3, '2016-06-01', '9999-12-31'),
  (1, 2, '2018-03-23', '2018-08-06'),
  (1, 3, '2018-08-07', '2018-08-21'),
  (1, 2, '2018-08-22', '2018-09-19'),
  (1, 2, '2018-09-20', '9999-12-31');
10 rows affected

select id, rating, min(rating_from), max(rating_to)
from (select t.*,
             sum(case when dateadd(day, 1, prev_rating_to) >= rating_from then 0 else 1 end) over
                 (partition by id, rating order by rating_from) as grp
      from (select t.*,
                   max(rating_to) over (partition by id, rating
                                        order by rating_from
                                        rows between unbounded preceding and 1 preceding
                                       ) as prev_rating_to
            from tbl t
           ) t
     ) t
group by id, rating, grp
order by id, rating, min(rating_from)


id
rating
(No column name)
(No column name)


1
2
23/05/2014 00:00:00
06/08/2018 00:00:00

1
2
22/08/2018 00:00:00
31/12/9999 00:00:00

1
3
07/08/2018 00:00:00
21/08/2018 00:00:00

2
1
01/06/2016 00:00:00
31/12/9999 00:00:00

3
3
01/06/2016 00:00:00
31/12/9999 00:00:00


delete from tbl where id = 1 and rating = 2 and rating_from = '2016-06-01'
1 rows affected

select * from tbl order by id, rating, rating_from


id
rating
rating_from
rating_to


1
2
23/05/2014 00:00:00
13/04/2015 00:00:00

1
2
14/04/2015 00:00:00
02/06/2015 00:00:00

1
2
03/06/2015 00:00:00
31/05/2016 00:00:00

1
2
23/03/2018 00:00:00
06/08/2018 00:00:00

1
2
22/08/2018 00:00:00
19/09/2018 00:00:00

1
2
20/09/2018 00:00:00
31/12/9999 00:00:00

1
3
07/08/2018 00:00:00
21/08/2018 00:00:00

2
1
01/06/2016 00:00:00
31/12/9999 00:00:00

3
3
01/06/2016 00:00:00
31/12/9999 00:00:00


select id, rating, min(rating_from), max(rating_to)
from (select t.*,
             sum(case when dateadd(day, 1, prev_rating_to) >= rating_from then 0 else 1 end) over
                 (partition by id, rating order by rating_from) as grp
      from (select t.*,
                   max(rating_to) over (partition by id, rating
                                        order by rating_from
                                        rows between unbounded preceding and 1 preceding
                                       ) as prev_rating_to
            from tbl t
           ) t
     ) t
group by id, rating, grp
order by id, rating, min(rating_from)


id
rating
(No column name)
(No column name)


1
2
23/05/2014 00:00:00
31/05/2016 00:00:00

1
2
23/03/2018 00:00:00
06/08/2018 00:00:00

1
2
22/08/2018 00:00:00
31/12/9999 00:00:00

1
3
07/08/2018 00:00:00
21/08/2018 00:00:00

2
1
01/06/2016 00:00:00
31/12/9999 00:00:00

3
3
01/06/2016 00:00:00
31/12/9999 00:00:00


select *
from (select t.*,
             sum(case when dateadd(day, 1, prev_rating_to) >= rating_from then 0 else 1 end) over
                 (partition by id, rating order by rating_from) as grp
      from (select t.*,
                   max(rating_to) over (partition by id, rating
                                        order by rating_from
                                        rows between unbounded preceding and 1 preceding
                                       ) as prev_rating_to
            from tbl t
           ) t
     ) t
order by id, rating, rating_from


id
rating
rating_from
rating_to
prev_rating_to
grp


1
2
23/05/2014 00:00:00
13/04/2015 00:00:00
null
1

1
2
14/04/2015 00:00:00
02/06/2015 00:00:00
13/04/2015 00:00:00
1

1
2
03/06/2015 00:00:00
31/05/2016 00:00:00
02/06/2015 00:00:00
1

1
2
23/03/2018 00:00:00
06/08/2018 00:00:00
31/05/2016 00:00:00
2

1
2
22/08/2018 00:00:00
19/09/2018 00:00:00
06/08/2018 00:00:00
3

1
2
20/09/2018 00:00:00
31/12/9999 00:00:00
19/09/2018 00:00:00
3

1
3
07/08/2018 00:00:00
21/08/2018 00:00:00
null
1

2
1
01/06/2016 00:00:00
31/12/9999 00:00:00
null
1

3
3
01/06/2016 00:00:00
31/12/9999 00:00:00
null
1

id	rating	(No column name)	(No column name)
1	2	23/05/2014 00:00:00	06/08/2018 00:00:00
1	2	22/08/2018 00:00:00	31/12/9999 00:00:00
1	3	07/08/2018 00:00:00	21/08/2018 00:00:00
2	1	01/06/2016 00:00:00	31/12/9999 00:00:00
3	3	01/06/2016 00:00:00	31/12/9999 00:00:00

id	rating	rating_from	rating_to
1	2	23/05/2014 00:00:00	13/04/2015 00:00:00
1	2	14/04/2015 00:00:00	02/06/2015 00:00:00
1	2	03/06/2015 00:00:00	31/05/2016 00:00:00
1	2	23/03/2018 00:00:00	06/08/2018 00:00:00
1	2	22/08/2018 00:00:00	19/09/2018 00:00:00
1	2	20/09/2018 00:00:00	31/12/9999 00:00:00
1	3	07/08/2018 00:00:00	21/08/2018 00:00:00
2	1	01/06/2016 00:00:00	31/12/9999 00:00:00
3	3	01/06/2016 00:00:00	31/12/9999 00:00:00

id	rating	rating_from	rating_to	prev_rating_to	grp
1	2	23/05/2014 00:00:00	13/04/2015 00:00:00	null	1
1	2	14/04/2015 00:00:00	02/06/2015 00:00:00	13/04/2015 00:00:00	1
1	2	03/06/2015 00:00:00	31/05/2016 00:00:00	02/06/2015 00:00:00	1
1	2	23/03/2018 00:00:00	06/08/2018 00:00:00	31/05/2016 00:00:00	2
1	2	22/08/2018 00:00:00	19/09/2018 00:00:00	06/08/2018 00:00:00	3
1	2	20/09/2018 00:00:00	31/12/9999 00:00:00	19/09/2018 00:00:00	3
1	3	07/08/2018 00:00:00	21/08/2018 00:00:00	null	1
2	1	01/06/2016 00:00:00	31/12/9999 00:00:00	null	1
3	3	01/06/2016 00:00:00	31/12/9999 00:00:00	null	1