Recursive SQL query to find the parent record or first and last or latest records

When we have a scenario to pick up first and  last record/hierarchy from a table which maintains history of changes(EmployeeHistory_Table).

Hierarchy_Table

EmployeeHistory_Table

expected output:

below is the sql for this kind of scenario:

CREATE TABLE #emp_RoleChanges (
emp_name VARCHAR(30),
current_role VARCHAR(30) ,
new_role VARCHAR(30)
);

INSERT INTO #emp_RoleChanges (emp_name , current_role , new_role )
VALUES
(‘Dan’, ‘Trainee_System_Engineer’, ‘System_Engineer’),
(‘Dan’, ‘System_Engineer’, ‘Senior_Software_Eng’),
(‘Dan’, ‘Senior_Software_Eng’, ‘Manager’),
(‘Tom’, ‘System_Engineer’, ‘Senior_Software_Eng’),
(‘Tom’, ‘Senior_Software_Eng’, ‘Team_Leader’),
(‘Jack’, ‘System_Engineer’, ‘Team_Leader’),
(‘Jack’, ‘Team_Leader’, ‘Manager’),
(‘Jack’, ‘Manager’, ‘Trainee_System_Engineer’)
;

select * from #emp_RoleChanges
;
WITH q AS (
SELECT 1 AS LEVEL, emp_name , current_role , new_role
FROM #emp_RoleChanges
UNION ALL
SELECT LEVEL + 1, q.emp_name, q.current_role, u.new_role
FROM q
INNER JOIN #emp_RoleChanges u ON u.current_role = q.new_role and u.emp_name = q.emp_name
)
–select * from q
–order by 2,1

SELECT –q.LEVEL,
q.emp_name , q.current_role started_role, q.new_role current_role
FROM q
where q.level = (select max(p.level) from q p where p.emp_name = q.emp_name)