Tom changed his job role many times

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