SQL Server 2019

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

create table codes (EmpID int, DeptCode int, FromDate datetime, ToDate datetime)
insert into codes values 
(201, 40, '2016-08-01 00:00:00.000', '2017-08-31 00:00:00.000'),
(201, 40, '2017-08-01 00:00:00.000', '2018-09-30 00:00:00.000'),
(201, 80, '2018-10-01 00:00:00.000', '2019-05-31 00:00:00.000'),
(201, 80, '2019-06-01 00:00:00.000', '2020-06-30 00:00:00.000'),
(201, 80, '2020-07-01 00:00:00.000', '2022-02-28 00:00:00.000'),
(201, 40, '2022-03-01 00:00:00.000', '9999-12-31 00:00:00.000'),
(302, 10, '2022-01-01 00:00:00.000', '9999-12-31 00:00:00.000')

select * from codes


EmpID
DeptCode
FromDate
ToDate


201
40
2016-08-01 00:00:00.000
2017-08-31 00:00:00.000

201
40
2017-08-01 00:00:00.000
2018-09-30 00:00:00.000

201
80
2018-10-01 00:00:00.000
2019-05-31 00:00:00.000

201
80
2019-06-01 00:00:00.000
2020-06-30 00:00:00.000

201
80
2020-07-01 00:00:00.000
2022-02-28 00:00:00.000

201
40
2022-03-01 00:00:00.000
9999-12-31 00:00:00.000

302
10
2022-01-01 00:00:00.000
9999-12-31 00:00:00.000


-- step one, get data that we can use for our group by
select t.*,
       row_number() over (partition by t.EmpID, DeptCode order by t.EmpID, t.FromDate) as seqnum_na,
       row_number() over (partition by t.EmpID order by t.EmpID, t.FromDate) as seqnum_n
from   codes t


EmpID
DeptCode
FromDate
ToDate
seqnum_na
seqnum_n


201
40
2016-08-01 00:00:00.000
2017-08-31 00:00:00.000
1
1

201
40
2017-08-01 00:00:00.000
2018-09-30 00:00:00.000
2
2

201
80
2018-10-01 00:00:00.000
2019-05-31 00:00:00.000
1
3

201
80
2019-06-01 00:00:00.000
2020-06-30 00:00:00.000
2
4

201
80
2020-07-01 00:00:00.000
2022-02-28 00:00:00.000
3
5

201
40
2022-03-01 00:00:00.000
9999-12-31 00:00:00.000
3
6

302
10
2022-01-01 00:00:00.000
9999-12-31 00:00:00.000
1
1


-- step 2, gather our data using our calculated group by
select i.EmpID, i.DeptCode, min(i.FromDate) as FromDate, max(i.ToDate) as ToDate
from (select t.*,
             row_number() over (partition by t.EmpID, DeptCode order by t.EmpID, t.FromDate) as seqnum_na,
             row_number() over (partition by t.EmpID order by t.EmpID, t.FromDate) as seqnum_n
      from   codes t
     ) i
group by (seqnum_n - seqnum_na), EmpID, DeptCode
order by i.EmpID, min(i.FromDate), i.DeptCode 



EmpID
DeptCode
FromDate
ToDate


201
40
2016-08-01 00:00:00.000
2018-09-30 00:00:00.000

201
80
2018-10-01 00:00:00.000
2022-02-28 00:00:00.000

201
40
2022-03-01 00:00:00.000
9999-12-31 00:00:00.000

302
10
2022-01-01 00:00:00.000
9999-12-31 00:00:00.000


select i.EmpID, i.DeptCode, min(i.FromDate) as FromDate, max(i.ToDate) as ToDate
from (select t.*,
             row_number() over (partition by t.EmpID order by t.EmpID, t.FromDate)
             -
             row_number() over (partition by t.EmpID, DeptCode order by t.EmpID, t.FromDate) 
             as grouper
      from   codes t
     ) i
group by grouper, EmpID, DeptCode
order by i.EmpID, min(i.FromDate), i.DeptCode 



EmpID
DeptCode
FromDate
ToDate


201
40
2016-08-01 00:00:00.000
2018-09-30 00:00:00.000

201
80
2018-10-01 00:00:00.000
2022-02-28 00:00:00.000

201
40
2022-03-01 00:00:00.000
9999-12-31 00:00:00.000

302
10
2022-01-01 00:00:00.000
9999-12-31 00:00:00.000

EmpID	DeptCode	FromDate	ToDate
201	40	2016-08-01 00:00:00.000	2017-08-31 00:00:00.000
201	40	2017-08-01 00:00:00.000	2018-09-30 00:00:00.000
201	80	2018-10-01 00:00:00.000	2019-05-31 00:00:00.000
201	80	2019-06-01 00:00:00.000	2020-06-30 00:00:00.000
201	80	2020-07-01 00:00:00.000	2022-02-28 00:00:00.000
201	40	2022-03-01 00:00:00.000	9999-12-31 00:00:00.000
302	10	2022-01-01 00:00:00.000	9999-12-31 00:00:00.000