Blog

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

role hierarchies

Trainee_System_Engineer

System_Engineer

Senior_Software_Eng

Team_Leader

Manager

Senior_Exec

 

EmployeeHistory_Table

emp_name	current_role	new_role
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	Senior_Exec

 

expected output:

emp_name	started_role	current_role
Jack	System_Engineer	Trainee_System_Engineer
Tom	System_Engineer	Team_Leader
Dan	Trainee_System_Engineer	Manager

 

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)