• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 12, 2020
  • Reading time:4 mins read
You are currently viewing SQL Query to get the Time in Job and Position
SQL Query to get the Time in Job and Position

In this article we will look into how to get the Time in Job and Position information for an employee from assignment information.

Want to learn Fusion Technical tools? Checkout this article

You might also be interested in the below posts on BI Publisher:

Even though it is possible to get this information using OTBI we cannot achieve both Time in Job and Time in Position in a single OTBI analysis and we do not have the flexibility to choose if you want to group by the assignment or not. So it’s better to get this information is through a BI Report if you have some hands-on with SQL Query.

SQL Query to get Time in Job and Time in Position Information:

SELECT papf.person_number
,paam.assignment_number
,paam.assignment_status_type
,pjfv.name Job_Name
,hapfv.name Position_Name
,round((
months_between(sysdate, (
SELECT MIN(paam1.effective_start_date)
FROM per_all_assignments_m paam1
WHERE paam1.person_id = paam.person_id
AND paam1.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam1.assignment_type NOT IN ('ET','CT','PT')
AND paam1.job_id = paam.job_id
AND NOT EXISTS (
SELECT 'x'
FROM per_all_assignments_m paam2
WHERE paam2.person_id = paam.person_id
AND paam2.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam2.job_id <> paam1.job_id
AND paam2.assignment_type NOT IN ('ET','CT','PT')
AND paam2.effective_start_date > paam1.effective_start_date
)
)) / 12
), 2) time_in_job_years
,round((
months_between(sysdate, (
SELECT MIN(paam1.effective_start_date)
FROM per_all_assignments_m paam1
WHERE paam1.person_id = paam.person_id
AND paam1.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam1.assignment_type NOT IN ('ET','CT','PT')
AND paam1.position_id = paam.position_id
AND NOT EXISTS (
SELECT 'x'
FROM per_all_assignments_m paam2
WHERE paam2.person_id = paam.person_id
AND paam2.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam2.position_id <> paam1.position_id
AND paam2.assignment_type NOT IN ('ET','CT','PT')
AND paam2.effective_start_date > paam1.effective_start_date
)
)) / 12
), 2) time_in_position_years
FROM per_all_assignments_m paam
,per_all_people_f papf
,HR_ALL_POSITIONS_F_VL hapfv
,PER_JOBS_F_VL pjfv
WHERE papf.person_id = paam.person_id
AND paam.assignment_type NOT IN ('ET','CT','PT')
AND paam.assignment_status_type = 'ACTIVE'
AND paam.primary_flag = 'Y'
AND paam.effective_latest_change = 'Y'
AND paam.position_id = hapfv.position_id(+)
AND paam.job_id = pjfv.job_id(+)
AND sysdate BETWEEN pjfv.effective_start_date(+)
AND pjfv.effective_end_date(+)
AND sysdate BETWEEN hapfv.effective_start_date(+)
AND hapfv.effective_end_date(+)
AND sysdate BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND sysdate BETWEEN paam.effective_start_date
AND paam.effective_end_date

Tip: This query gives you the information of the Time in Job and Position for the primary active assignment as of system date. If you want to modify any criteria feel free to modify and use it as per your requirement.