Re: Change detection

just change lag(department) over(order by year) to lag(department)
over(partition by name order by year)

Atenciosamente,

Em sex., 9 de dez. de 2022 às 11:15, Shaozhong SHI <shishaozhong(at)gmail(dot)com>
escreveu:

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