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 t
(ID	varchar(3) ,Rec	int , Date date)

insert into t values
('40A',	1022,	'02/03/2022'),
('40A',	1029,	'02/03/2022'),
('40A',	5542,	'04/11/2022'),
('40A',	7693,	'04/11/2022'),
('40A',	1580,	'06/10/2022'),
('40A',	9700,	'08/15/2022'),
('88A',	9911,	'09/10/2022'),
('88A',	1033,	'12/15/2022'),
('88A',	1443,	'03/15/2023')
9 rows affected

with cte as
(SELECT 
    ID, Rec, Date,
    (select top 1 date from t t1 where t1.id = t.id and t1.date < t.date order by t1.date desc) as Previous_Rec
FROM 
    t
)
select cte.*,t.rec,t.date 
from cte
left join t on t.date = cte.previous_rec and t.id = cte.id
order by cte.id, cte.date


ID
Rec
Date
Previous_Rec
rec
date


40A
1022
2022-02-03
null
null
null

40A
1029
2022-02-03
null
null
null

40A
5542
2022-04-11
2022-02-03
1022
2022-02-03

40A
5542
2022-04-11
2022-02-03
1029
2022-02-03

40A
7693
2022-04-11
2022-02-03
1022
2022-02-03

40A
7693
2022-04-11
2022-02-03
1029
2022-02-03

40A
1580
2022-06-10
2022-04-11
5542
2022-04-11

40A
1580
2022-06-10
2022-04-11
7693
2022-04-11

40A
9700
2022-08-15
2022-06-10
1580
2022-06-10

88A
9911
2022-09-10
null
null
null

88A
1033
2022-12-15
2022-09-10
9911
2022-09-10

88A
1443
2023-03-15
2022-12-15
1033
2022-12-15

ID	Rec	Date	Previous_Rec	rec	date
40A	1022	2022-02-03	null	null	null
40A	1029	2022-02-03	null	null	null
40A	5542	2022-04-11	2022-02-03	1022	2022-02-03
40A	5542	2022-04-11	2022-02-03	1029	2022-02-03
40A	7693	2022-04-11	2022-02-03	1022	2022-02-03
40A	7693	2022-04-11	2022-02-03	1029	2022-02-03
40A	1580	2022-06-10	2022-04-11	5542	2022-04-11
40A	1580	2022-06-10	2022-04-11	7693	2022-04-11
40A	9700	2022-08-15	2022-06-10	1580	2022-06-10
88A	9911	2022-09-10	null	null	null
88A	1033	2022-12-15	2022-09-10	9911	2022-09-10
88A	1443	2023-03-15	2022-12-15	1033	2022-12-15