Associating an ID with a Payroll Code

• SQL for HR Access

There are situations where the only entry point is a payroll code. In this case, the need was to find a sample ID for a selection of codes. Once we have the codes, it becomes possible to identify the associated triggering rules.

It was worth taking a few minutes to try and find an SQL query that does the job…

After some testing, here is an SQL query that, for all calculated codes in the PRDB with a display position on the payslip, associates an ID:

with rub_et_mat as (select b.codrub,
a.perpai,
a.matric
from zx00 a,
zx8k b
where a.nudoss = b.nudoss
and b.codrub in (select a.cdcode from zd00 a, zdao b where a.nudoss = b.nudoss and b.rgposb <> ' ')
and b.perpai = 'MT202201'
),
rub_et_mat_rn as (
select codrub, matric, perpai, row_number() over (partition by codrub order by perpai desc ) as rn
from rub_et_mat )
select codrub, matric, perpai from rub_et_mat_rn where rn = 1
;