Beaufort: check for duplicate pers_nr on the same sofi_nr

In every HR-system you can have the situation that an employee leaves the company and re-joins about a year later. The company can register the user using the old record or create an entirely new employee record. In the latter case, HR to AD synchronizations will not work properly unless you check for this situation. Using the sofi_nr, which is unique for an identity, you can see if there are other employee records with a later contract end date for the same sofi_nr.

Beaufort query
The first record returned on the below query will contain the latest contract end date (+1000 days from current date if empty = unlimited). If this date is different from the date you queried from the already known pers_nr, you can conclude the pers_nr you used is outdated.

select convert(varchar, t1.pers_nr) as pers_nr,
isnull(t1.uitdnst_dt, getdate() + 1000)
from dpic300 t1,
dpic300 t2,
dpib010 t3
where t1.pers_nr = t2.pers_nr
and t1.pers_nr = t3.pers_nr
and t3.sofi_nr = %sofi_nr%
group by t1.pers_nr,
having isnull(t1.uitdnst_dt, getdate() + 1000) = max(isnull(t2.uitdnst_dt, getdate() + 1000))
order by isnull(t1.uitdnst_dt, getdate() + 1000) desc

Geschreven door:

Arnout van der Vorst

Arnout van der Vorst is Identity Management Architect bij Tools4ever en al ruim 10 jaar in dienst. Arnout legt zich als Architect toe op het bedenken en ontwikkelen van nieuwe features, oplossingen en diensten van Tools4ever die aansluiten op de vraag uit de markt. Arnout studeerde Hogere Informatica aan de Hogeschool van Utrecht.