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,
t1.uitdnst_dt
having isnull(t1.uitdnst_dt, getdate() + 1000) = max(isnull(t2.uitdnst_dt, getdate() + 1000))
order by isnull(t1.uitdnst_dt, getdate() + 1000) desc

• beaufort
• hr-systeem