employee_number VARCHAR(40)

, old_employee_number VARCHAR(40)

, entity_id int

, original_start_date date

);

('5041', 'A0EH', 96, current_date+0)

, ('A0EH', null , 96, current_date-5)

, ('XXXX', null , 88, current_date-8)

;

SELECT employee_number, employee_number, entity_id, original_start_date, 1 FROM earliest_start_date WHERE old_employee_number IS NULL UNION ALL

SELECT new_tbl.employee_number, cte.original_no, cte.entity_id, cte.original_start_date, n+1

FROM earliest_start_date new_tbl

JOIN cte

ON cte.employee_number = new_tbl.old_employee_number

)

, xrows AS (

SELECT *, ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY n DESC) AS rn

FROM cte

)

SELECT * FROM xrows WHERE rn = 1

;