Subject: Change detection

From: Shaozhong SHI <[email-masked]>
Date: Fri, 9 Dec 2022 12:55:16 +0000

A record shows all staff member worked in different departments.

For instance, Tom was in sales for several years and got promoted to
management.   How to detect the time of this change?

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

Regards,

David

---

From: Stöcker, Martin<[email-masked]>
Date: Fri, 9 Dec 2022 13:02:10 +0000

select Name, Department, min(year), max(year) from data group by Name, Department

The meaning of Staff_ID is obscure because it’s not unique.

Mit freundlichen Grüßen

Martin Stöcker
-----------------------------------------
ETL Datenservice GmbH
Widdersdorfer Str. 415 | D-50933 Köln
Telefon: +49(0)2219544010
Fax: +49(0)2219544015
Email: [email-masked]

[etlds]

ETL Datenservice GmbH
Widdersdorfer Str. 415 · 50933 Köln
Geschäftsführer: Dr. Dirk Goldner, ppa. Melanie Lillich, ppa. Udo Heuschmann
Amtsgericht Köln · HRB 75439 · USt.-Id: DE 122 805 685
www.etl-datenservice.de<http://www.etl-datenservice.de>
Email: [email-masked]<mailto:[email-masked]>
Von: Shaozhong SHI <[email-masked]>
Gesendet: Freitag, 9. Dezember 2022 13:55
An: pgsql-sql <[email-masked]>
Betreff: Change detection

For instance, Tom was in sales for several years and got promoted to management.   How to detect the time of this change?

Data

Regards,

David

---

From: Marcos Pegoraro <[email-masked]>
Date: Fri, 9 Dec 2022 10:06:24 -0300

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

---

From: Shaozhong SHI <[email-masked]>
Date: Fri, 9 Dec 2022 13:14:15 +0000

Thanks, Marcos.  It worked well.  Regards, David

---

From: Shaozhong SHI <[email-masked]>
Date: Fri, 9 Dec 2022 14:15:06 +0000

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

---

From: Marcos Pegoraro <[email-masked]>
Date: Fri, 9 Dec 2022 14:00:08 -0300

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

Atenciosamente,

---

From: Shaozhong SHI <[email-masked]>
Date: Fri, 9 Dec 2022 19:32:48 +0000

That works well.

I just wonder whether we can tell Tom or Tim has worked in more than 1
department.  Apparently, PostgreSQL does not allow count(distinct
department) when window function is used.

Given this data set, can we do something like count(distinct) to provide an
answer to how many different department someone has worked in?

Regards,

David

---

From: Rob Sargent <[email-masked]>
Date: Fri, 9 Dec 2022 12:38:07 -0700

On 12/9/22 12:32, Shaozhong SHI wrote:
> Given this data set, can we do something like count(distinct) to 
> provide an answer to how many different department someone has worked in?
>
> Regards,
>
> David
Use the working query in a CTE and simply do the count(distinct 
department) on that, else there's no good place to put the number of 
departments per person.

Pretty sure you've been asked before but Please don't top post.