Consolidate overlapping and consecutive date range in SQL

This should do what you want. This was done using SQL Server so you should be safe

create table #main
(
id int not null identity(1,1),
ref int null,
ref2 int null,
year int null,
startdate datetime null,
enddate datetime null
)

insert into #main (ref, ref2, year,startdate,enddate) values( 34321,111,2021, '20210201','20210501')
insert into #main (ref, ref2, year,startdate,enddate) values( 12345,111,2021, '20210101','20210228')
insert into #main (ref, ref2, year,startdate,enddate) values( 21111,111,2021, '20210301','20210531')
insert into #main (ref, ref2, year,startdate,enddate) values( 21111,111,2021, '20210601','20210801')
insert into #main (ref, ref2, year,startdate,enddate) values( 21111,111,2021, '20210401','20211001')
insert into #main (ref, ref2, year,startdate,enddate) values( 12345,111,2021, '20210301','20210331')
insert into #main (ref, ref2, year,startdate,enddate) values( 34222,123,2021, '20210801','20210930')
insert into #main (ref, ref2, year,startdate,enddate) values( 34222,123,2021, '20211101','20211130')

select * from #main

select ref, max(ref2) as ref2, YEAR, MIN(startdate) as startdate, 
MAX(enddate) as enddate
from #main
group by ref, YEAR

Found this option to achieve my desired output...but in two steps. First consolidate the overlapping date ranges and then consolidate consecutive date ranges. But wanna know whether there is more efficient way to do this.

DECLARE @TempTableFinal TABLE
        (   
            Id  INT,
            EmployeeId              VARCHAR(50),
            CompanyId INT,
            StartDate   DATETIME,
            EndDate DATETIME    
        );
DECLARE @TempTableFinal2 TABLE
(   
    EmployeeId              VARCHAR(50),
    CompanyId       INT,
    StartDate   DATETIME,
    EndDate DATETIME    
);

INSERT INTO @TempTableFinal2

SELECT
s1.EmployeeId,s1.CompanyId,
s1.StartDate,
MIN(t1.EndDate) AS EndDate
FROM test2 s1
INNER JOIN test2 t1 ON s1.EmployeeId = t1.EmployeeId and s1.CompanyId = t1.CompanyId and s1.StartDate <= t1.EndDate
AND NOT EXISTS(SELECT * FROM test2 t2
WHERE t1.EmployeeId = t2.EmployeeId and t1.CompanyId = t2.CompanyId and t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate)
WHERE NOT EXISTS(SELECT * FROM test2 s2
WHERE s1.EmployeeId = s2.EmployeeId and s1.CompanyId = s2.CompanyId and s1.StartDate > s2.StartDate AND s1.StartDate <= s2.EndDate)
GROUP BY s1.EmployeeId,s1.StartDate,s1.CompanyId
ORDER BY s1.StartDate


 ;with cte as (
    select EmployeeId,CompanyId,StartDate, EndDate
    from @TempTableFinal2
    union all
    select t.EmployeeId,t.CompanyId,cte.StartDate, t.EndDate
    from cte
    join @TempTableFinal2 t on cte.EmployeeId = t.EmployeeId and cte.CompanyId = t.CompanyId and DateAdd(d,1,cte.EndDate) = t.EnrollmentStart
), cte2 as (
    select *, rn = row_number() over (partition by EmployeeId, EndDate order by StartDate)
    from cte
)

INSERT INTO @TempTableFinal
select ROW_NUMBER() OVER (ORDER BY EmployeeId, StartDate) as Id,EmployeeId,CompanyId,StartDate, max(EndDate) enddate
from cte2
where rn=1
group by EmployeeId, StartDate,CompanyId
order by EmployeeId, StartDate;

select * from @TempTableFinal