Re: Change detection

How about finding all changes for all people in a large record set?

See the follwoing:

David

1 Tom Sales 1990
2 Tom Sales 1991
3 Tom Sales 1991
4 Tom Management 1992
5 Tom Management 1992
6 Tim finance 1982
7 Tim finance 1983
8 Tim management 1984
9 Tim management 1985

On Fri, 9 Dec 2022 at 13:06, Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> wrote:

> 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)
>
>