SQL Server 2022

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

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE emp(
   Id         INTEGER  NOT NULL 
  ,SD         DATE  NOT NULL
  ,ED         DATE  NOT NULL
  ,Status     VARCHAR(1) NOT NULL
  ,Assignment VARCHAR(4)
);
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (1,'1/1/2020','1/15/2020','A','A1');
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (1,'1/16/2020','2/1/2020','T',NULL);
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (1,'2/2/2020','3/20/2021','A',NULL);
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (1,'3/21/2021','10/1/2022','A','B6');
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (3,'10/15/2022','5/12/2023','A','A1');
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (2,'1/3/2022','2/1/2022','A','B2');
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (2,'2/2/2022','11/1/2023','T',NULL);
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (1,'10/2/2022','1/1/2023','T',NULL);
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (1,'1/2/2023','5/12/2023','A','A1');
INSERT INTO emp(Id,SD,ED,Status,Assignment) VALUES (2,'11/2/2023','5/12/2023','A','A8');
10 rows affected

select e.*,
   min(case when status = 'A' then sd end) over(partition by id) original_hire_date,
   min(case when status = 'A' then sd end) over(partition by id, grp) recent_hire_date
from (
    select e.*, 
        sum(case when status = 'A' and lag_status = 'T' then 1 else 0 end) over(partition by id order by sd) grp
    from (
        select e.*, lag(status) over(partition by id order by sd) lag_status
        from emp e
    ) e
) e
order by id, sd


Id
SD
ED
Status
Assignment
lag_status
grp
original_hire_date
recent_hire_date


1
2020-01-01
2020-01-15
A
A1
null
0
2020-01-01
2020-01-01

1
2020-01-16
2020-02-01
T
null
A
0
2020-01-01
2020-01-01

1
2020-02-02
2021-03-20
A
null
T
1
2020-01-01
2020-02-02

1
2021-03-21
2022-10-01
A
B6
A
1
2020-01-01
2020-02-02

1
2022-10-02
2023-01-01
T
null
A
1
2020-01-01
2020-02-02

1
2023-01-02
2023-05-12
A
A1
T
2
2020-01-01
2023-01-02

2
2022-01-03
2022-02-01
A
B2
null
0
2022-01-03
2022-01-03

2
2022-02-02
2023-11-01
T
null
A
0
2022-01-03
2022-01-03

2
2023-11-02
2023-05-12
A
A8
T
1
2022-01-03
2023-11-02

3
2022-10-15
2023-05-12
A
A1
null
0
2022-10-15
2022-10-15


Warning: Null value is eliminated by an aggregate or other SET operation.

Id	SD	ED	Status	Assignment	lag_status	grp	original_hire_date	recent_hire_date
1	2020-01-01	2020-01-15	A	A1	null	0	2020-01-01	2020-01-01
1	2020-01-16	2020-02-01	T	null	A	0	2020-01-01	2020-01-01
1	2020-02-02	2021-03-20	A	null	T	1	2020-01-01	2020-02-02
1	2021-03-21	2022-10-01	A	B6	A	1	2020-01-01	2020-02-02
1	2022-10-02	2023-01-01	T	null	A	1	2020-01-01	2020-02-02
1	2023-01-02	2023-05-12	A	A1	T	2	2020-01-01	2023-01-02
2	2022-01-03	2022-02-01	A	B2	null	0	2022-01-03	2022-01-03
2	2022-02-02	2023-11-01	T	null	A	0	2022-01-03	2022-01-03
2	2023-11-02	2023-05-12	A	A8	T	1	2022-01-03	2023-11-02
3	2022-10-15	2023-05-12	A	A1	null	0	2022-10-15	2022-10-15