Finding a consecutive date ranges in ms sql server

Question

I searched the forum posts for similar issue to mine but could not find one. There are other posts on gaps and islands problem with date ranges, but not that works for my case.

Here is the create table and insert queries:

create table test_0907 (PRODUCT_TIER4_DESC nvarchar(15), PRODUCT_TIER5_DESC nvarchar(15), BEGIN_EFFECTIVE_DT date, END_EFFECTIVE_DT date)

insert into test_0907 values ('Cloud', 'Other', '2018-03-01 00:00:00.000', '2020-12-31 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2019-07-01 00:00:00.000', '2020-12-31 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2020-12-01 00:00:00.000', '2020-12-31 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2021-01-01 00:00:00.000', '2021-06-30 00:00:00.000')
insert into test_0907 values ('Other', 'Other', '2021-09-01 00:00:00.000', '2021-09-30 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2021-09-01 00:00:00.000', '9999-12-31 00:00:00.000')
insert into test_0907 values ('Cloud', 'Other', '2022-02-01 00:00:00.000', '9999-12-31 00:00:00.000')

And my output should be :

PRODUCT_TIER4_DESC  | PRODUCT_TIER5_DESC    | BEGIN_EFFECTIVE_DT    | END_EFFECTIVE_DT      |
--------------------------------------------------------------------------------------------|
Cloud               | Other                 | 2018-03-01            | 2021-06-30            |
Other               | Other                 | 2021-09-01            | 2021-09-30            |
Cloud               | Other                 | 2021-09-01            | 9999-12-31            |

I am trying to use lag or lead but cant get the desired output. Is there any other analytical function I should be looking at?

EDIT : This is not same as the related post because the start date in the other post does not overlap. In the example I have the begin_effective_dt for 1st 3 rows has value less than the end_effective_dt of the 3rd row. Hence, this is not the same data set and query will also be different.

Answer 1

Try the following:

WITH CTE AS
  (
  SELECT *, 
     ROW_NUMBER() OVER (PARTITION BY PRODUCT_TIER5_DESC ORDER BY BEGIN_EFFECTIVE_DT) - 
     ROW_NUMBER() OVER (PARTITION BY PRODUCT_TIER5_DESC,PRODUCT_TIER4_DESC ORDER BY BEGIN_EFFECTIVE_DT)  GRP
  FROM test_0907
  )
SELECT PRODUCT_TIER4_DESC, PRODUCT_TIER5_DESC, 
  MIN(BEGIN_EFFECTIVE_DT) BEGIN_EFFECTIVE_DT, MAX(END_EFFECTIVE_DT) END_EFFECTIVE_DT
FROM CTE
GROUP BY PRODUCT_TIER4_DESC, PRODUCT_TIER5_DESC,GRP

See a demo.