SQL Server 2022

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

12 rows affected

with precedence_match as (
select *,
       -- negative is just to prevent collisions with fuzzy_match
       (-1)*row_number() over (
         partition by 1 
             order by RecordDate) absolute_order_by_record_date,
       coalesce(
         (
         select max(t2.PatientUID)
           from PatientData t2
          where t1.PatientUID = t2.PatientUID
         ),
         (
         select max(t2.PatientUID)
           from PatientData t2
          where t1.PatientMRN = t2.PatientMRN
         ),
         (
         select max(t2.PatientUID)
           from PatientData t2
          where t1.PatientSSN = t2.PatientSSN
         ),
         t1.PatientUID
       ) as fuzzy_match
  from PatientData t1)
select RecordDate,
       PatientUID,
       PatientMRN,
       PatientSSN,
       min(RecordDate) over ( 
         partition by coalesce(fuzzy_match,absolute_order_by_record_date)) as calculated_min_date_for_reference
  from precedence_match
 order 
    by min(RecordDate) over ( 
         partition by coalesce(fuzzy_match,absolute_order_by_record_date)),
       RecordDate

RecordDate	PatientUID	PatientMRN	PatientSSN	calculated_min_date_for_reference
2023-01-01	1	ABC	111111111	2023-01-01
2023-04-01	1	null	null	2023-01-01
2023-07-01	null	ABC	null	2023-01-01
2023-10-01	null	null	111111111	2023-01-01
2023-02-01	null	null	null	2023-02-01
2023-03-01	2	DEF	222222222	2023-03-01
2023-06-01	2	null	null	2023-03-01
2023-09-01	null	DEF	null	2023-03-01
2023-12-01	null	null	222222222	2023-03-01
2023-05-01	null	null	null	2023-05-01
2023-08-01	null	null	null	2023-08-01
2023-11-01	null	null	null	2023-11-01

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