Re: Change detection

>
> Data
>
> Staff_ID Name Department Year
> 1 Tom Sales 1990
> 2 Tom Sales 1991
> 3 Tom Sales 1991
> 4 Tom Management 1992
> 4 Tom Management 1992
>
> select *, coalesce(lag(department) over(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)) as x(Staff_ID, Name, Department, Year);
staff_id | name | department | year | changed
----------+------+------------+------+---------
1 | Tom | Sales | 1990 | f
2 | Tom | Sales | 1991 | f
3 | Tom | Sales | 1991 | f
4 | Tom | Management | 1992 | t
4 | Tom | Management | 1992 | f
(5 rows)