Finding couples of occurrences – Postgresql

I need to find possible couplings of employees and department that they worked for. I need to include only the couplings of 2 different departments, so if an employee worked for more than 2 departments, I need to divide them into couplings of 2 departments to show the transfers. Also the period of the contract of the employee at the first department must be earlier than the contract at the second department. I need to list the values as “first_name”, “last_name, “deptnr1”, “dept1”, “deptnr2”, “dept2”.

For example John Doe worked for department A(deptnr 9) from 01/01/2016 to 06/06/2016 and for department B(deptnr 3) from 10/06/2016 to 12/12/2017, the result should be like :

John, Doe, 9, A, 3, B

If he then returned to his job at department A, there should be another coupling like this to make his 2nd transfer visible:

John, Doe, 3, B, 9, A

so if he transfers around, we should have as many couplings of departments as possible, in this case 2 transfers, thus 2 couplings out of 3 departments(A => B => A so A,B/B,A).

I have 4 tables.

Person (PK email, first_name, last_name, FK postcode, FK place_name)

Employee(PK employeenr, FK email)

Department(PK departmentnr, name, FK postcode, FK place_name)

Contract(PK periode_begin, PK periode_end, FK departmentnr, FK employeenr)

I have tried this but I don’t know how to make use of aliases to take values from let’s say department.name and put them on other columns as name1 and name2. Also I couldn’t figure out a way to make couplings out of let’s say four transfers like (A=> B=> C=> D=> E TO A,B/B,C/C,D/D,E).

SELECT
    first_name, 
    last_name, 
    d1.departmentnr AS deptnr1, 
    d1.name AS dept1,
    d2.departmentnr AS deptnr2, 
    d2.name AS dept2,
FROM person
INNER JOIN employee ON employee.email=person.email
INNER JOIN contract ON contract.employeenr = employee.employeenr
INNER JOIN department d1 ON department.departmentnr = contract.departmentnr
where contract.employeenr in 
(SELECT employeenr FROM contract 
GROUP BY employeenr HAVING COUNT(employeenr)>1 
AND COUNT(employeenr)>1)

Answer

Use the window function lead()

select
    first_name,
    last_name,
    deptnr1,
    dept1,
    deptnr2,
    dept2
from (
    select
        first_name,
        last_name,
        departmentnr as deptnr1,
        name as dept1,
        lead(departmentnr) over w as deptnr2,
        lead(name) over w as dept2,
        periode_begin
    from person p
    join employee e using(email)
    join contract c using(employeenr)
    join department d using(departmentnr)
    window w as (partition by email order by periode_begin)
    ) s
where deptnr2 is not null
order by first_name, last_name, periode_begin

Read also about window functions in the documentation.

Advertisement

Answer