Tom has changed his job role many times.
We want to pick up only the records before and after his role change to show what happened.
select *, coalesce(lag(department) over(partition by name order by year), department) <> department Changed
from (Values (1, 'Tom', 'Sales', 1990),(2, 'Tom', 'Sales', 1991),(3, 'Tom', 'Sales', 1991),(4, 'Tom', 'Management', 1992),(4, 'Tom', 'Management', 1992), (4, 'Tom', 'Sales', 1993),(4, 'Tim', 'Finance', 1981), (4, 'Tim', 'Finance', 1982), (4, 'Tim', 'Management', 1983), (4, 'Tim', 'Management', 1984)) as x(Staff_ID, Name, Department, Year);
Regards,
David