SQL - Comparing Job and Position Grades for Active Employees

SELECT
  papf.person_number,
  pjf.grade_code AS job_grade,
  pgf.grade_code AS position_grade
FROM fusion.per_all_people_f papf
JOIN fusion.per_all_assignments_m paam
  ON papf.person_id = paam.person_id
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
LEFT JOIN fusion.per_grades_f pjf
  ON paam.grade_id = pjf.grade_id
  AND TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date
LEFT JOIN fusion.hr_all_positions pos
  ON paam.position_id = pos.position_id
  AND TRUNC(SYSDATE) BETWEEN pos.effective_start_date AND pos.effective_end_date
LEFT JOIN fusion.per_grades_f pgf
  ON pos.entry_grade_id = pgf.grade_id
  AND TRUNC(SYSDATE) BETWEEN pgf.effective_start_date AND pgf.effective_end_date
WHERE
  paam.assignment_type = 'E'
  AND paam.primary_flag = 'Y'
  AND paam.assignment_status_type = 'ACTIVE'
  AND INSTR(',' || :person_number_list || ',', ',' || papf.person_number || ',') > 0
ORDER BY
  papf.person_number

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.