SQL Query to Get Department, Grade, Job details

This article will provide SQL query snippet to pull up Department, Grade, Job details. There are two scenarios :

1. Latest Department, Grade, Job with their name, effective date and other basic details.

2. Department, Grade, Job change complete history.

This article will cover both the scenarios.

Scenario 1 - Latest Department, Grade, Job with their name, effective date and other basic details.

select  distinct(B.PERSON_NUMBER),
(case when C.MIDDLE_NAMES is null then C.FIRST_NAME||' '||C.LAST_NAME
else
C.FIRST_NAME||' '||C.MIDDLE_NAMES||' '||C.LAST_NAME end) As "Full_Name",
A.ASS_ATTRIBUTE1 As "User Status",
TO_CHAR(T.DATE_START,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "Date of Joining",
A.ASSIGNMENT_STATUS_TYPE,
(SELECT DISTINCT(D.NAME) FROM PER_DEPARTMENTS D WHERE D.ORGANIZATION_ID = A.ORGANIZATION_ID AND D.EFFECTIVE_START_DATE = (SELECT MAX(D1.EFFECTIVE_START_DATE) FROM PER_DEPARTMENTS D1 WHERE D1.ORGANIZATION_ID = D.ORGANIZATION_ID)) AS "Department",
(select to_char(min(X.effective_start_date),'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') from per_all_assignments_m X where X.ORGANIZATION_ID in (select ORGANIZATION_ID from per_all_assignments_m Y where Y.person_id=X.person_id
and trunc(sysdate) between y.effective_start_date and y.effective_end_date
and  Y.assignment_type='E')
and X.person_id=A.person_id
and  X.assignment_type='E' ) as "Department Change Date",
(SELECT DISTINCT(O.NAME) FROM PER_JOBS_F_VL O WHERE O.JOB_ID = A.JOB_ID AND O.EFFECTIVE_START_DATE = (SELECT MAX(O1.EFFECTIVE_START_DATE) FROM PER_JOBS_F_VL O1 WHERE O1.JOB_ID = O.JOB_ID)) AS "Job",
(select to_char(min(X.effective_start_date),'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') from per_all_assignments_m X where X.job_id in (select job_id from per_all_assignments_m Y where Y.person_id=X.person_id
and trunc(sysdate) between y.effective_start_date and y.effective_end_date
and  Y.assignment_type='E')
and X.person_id=A.person_id
and  X.assignment_type='E' ) as "Job Change Date",
(select pg.name from per_grades pg where pg.grade_id=a.grade_id and rownum=1) AS "Grade",
(select to_char(min(X.effective_start_date),'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') from per_all_assignments_m X where X.grade_id in(select grade_id from per_all_assignments_m Y where Y.person_id=X.person_id
and trunc(sysdate) between y.effective_start_date and y.effective_end_date
and  Y.assignment_type='E')
and X.person_id=A.person_id
and  X.assignment_type='E' ) as "Grade Change Date",
(case when T.ACTUAL_TERMINATION_DATE is null then 'NA'
else 
TO_CHAR(T.ACTUAL_TERMINATION_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') end ) AS "Date of Separation"
FROM PER_ALL_ASSIGNMENTS_M A, PER_ALL_PEOPLE_F B, PER_PERSON_NAMES_F C, PER_PERIODS_OF_SERVICE T
WHERE A.PRIMARY_FLAG = 'Y'
AND A.PERSON_ID = B.PERSON_ID
AND A.PERSON_ID = C.PERSON_ID
AND C.NAME_TYPE = 'GLOBAL'
AND T.PERSON_ID = B.PERSON_ID
AND A.PERIOD_OF_SERVICE_ID=T.PERIOD_OF_SERVICE_ID
and a.SYSTEM_PERSON_TYPE in ('EMP','CWK')
and trunc(sysdate) between a.effective_start_date and a.effective_end_date
and trunc(sysdate) between b.effective_start_date and b.effective_end_date
and A.effective_latest_change = 'Y'
and trunc(sysdate) between c.effective_start_date and c.effective_end_date
and A.assignment_status_type in ('ACTIVE','INACTIVE')
and A.assignment_type='E' 
order by b.person_number desc

Scenario 2 - Department, Grade, Job change complete history.

(select  distinct(B.PERSON_NUMBER),
(case when C.MIDDLE_NAMES is null then C.FIRST_NAME||' '||C.LAST_NAME
else
C.FIRST_NAME||' '||C.MIDDLE_NAMES||' '||C.LAST_NAME end) As "Full_Name",
(select 'Job' from dual )as "ATTRIBUTE_CHANGE",
A.ASS_ATTRIBUTE1 As "User Status",
TO_CHAR(T.DATE_START,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "Date of Joining",
A.ASSIGNMENT_STATUS_TYPE,
TO_CHAR(a.effective_start_Date,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') as "DATE_CHANGE", j.name
from per_all_assignments_m a, PER_JOBS_F_VL j, PER_ALL_PEOPLE_F B, PER_PERSON_NAMES_F C, PER_PERIODS_OF_SERVICE T
where a.job_id=j.job_id
and A.assignment_type='E' 
AND A.PERSON_ID = B.PERSON_ID
AND A.PERSON_ID = C.PERSON_ID
AND C.NAME_TYPE = 'GLOBAL'
AND T.PERSON_ID = B.PERSON_ID
AND A.PERIOD_OF_SERVICE_ID=T.PERIOD_OF_SERVICE_ID
and a.SYSTEM_PERSON_TYPE in ('EMP','CWK')
and A.effective_start_Date = 
(select min(X.effective_start_date) from per_all_assignments_m X where X.job_id in (select Y.job_id from per_all_assignments_m Y, PER_JOBS_F_VL Z1 where Y.person_id=X.person_id
and  Y.assignment_type='E'
and Y.JOB_ID=Z1.JOB_ID)
and X.person_id=A.person_id
and  X.assignment_type='E'
and X.JOB_ID=A.JOB_ID)
)
union ALL
(
select  distinct(B.PERSON_NUMBER),
(case when C.MIDDLE_NAMES is null then C.FIRST_NAME||' '||C.LAST_NAME
else
C.FIRST_NAME||' '||C.MIDDLE_NAMES||' '||C.LAST_NAME end) As "Full_Name",
(select 'Grade' from dual )as "ATTRIBUTE_CHANGE",
A.ASS_ATTRIBUTE1 As "User Status",
TO_CHAR(T.DATE_START,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "Date of Joining",
A.ASSIGNMENT_STATUS_TYPE,
TO_CHAR(a.effective_start_Date,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') as "DATE_CHANGE", j.name
from per_all_assignments_m a, per_grades j, PER_ALL_PEOPLE_F B, PER_PERSON_NAMES_F C, PER_PERIODS_OF_SERVICE T
where a.grade_id=j.grade_id
and A.assignment_type='E' 
AND A.PERSON_ID = B.PERSON_ID
AND A.PERSON_ID = C.PERSON_ID
AND C.NAME_TYPE = 'GLOBAL'
AND T.PERSON_ID = B.PERSON_ID
AND A.PERIOD_OF_SERVICE_ID=T.PERIOD_OF_SERVICE_ID
and a.SYSTEM_PERSON_TYPE in ('EMP','CWK')
and A.effective_start_Date = 
(select min(X.effective_start_date) from per_all_assignments_m X where X.grade_id in (select Y.grade_id from per_all_assignments_m Y, per_grades Z1 where Y.person_id=X.person_id
and  Y.assignment_type='E'
and Y.grade_id=Z1.grade_id)
and X.person_id=A.person_id
and  X.assignment_type='E'
and X.grade_id=A.grade_id)
)
union ALL
(
select  distinct(B.PERSON_NUMBER),
(case when C.MIDDLE_NAMES is null then C.FIRST_NAME||' '||C.LAST_NAME
else
C.FIRST_NAME||' '||C.MIDDLE_NAMES||' '||C.LAST_NAME end) As "Full_Name",
(select 'Department' from dual )as "ATTRIBUTE_CHANGE",
A.ASS_ATTRIBUTE1 As "User Status",
TO_CHAR(T.DATE_START,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "Date of Joining",
A.ASSIGNMENT_STATUS_TYPE,
TO_CHAR(a.effective_start_Date,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') as "DATE_CHANGE", j.name
from per_all_assignments_m a, PER_DEPARTMENTS j, PER_ALL_PEOPLE_F B, PER_PERSON_NAMES_F C, PER_PERIODS_OF_SERVICE T
where a.organization_id=j.organization_id
and A.assignment_type='E' 
AND A.PERSON_ID = B.PERSON_ID
AND A.PERSON_ID = C.PERSON_ID
AND C.NAME_TYPE = 'GLOBAL'
AND T.PERSON_ID = B.PERSON_ID
AND A.PERIOD_OF_SERVICE_ID=T.PERIOD_OF_SERVICE_ID
and a.SYSTEM_PERSON_TYPE in ('EMP','CWK')
and A.effective_start_Date = 
(select min(X.effective_start_date) from per_all_assignments_m X where X.grade_id in (select Y.grade_id from per_all_assignments_m Y, PER_DEPARTMENTS Z1 where Y.person_id=X.person_id
and  Y.assignment_type='E'
and Y.organization_id=Z1.organization_id)
and X.person_id=A.person_id
and  X.assignment_type='E'
and X.organization_id=A.organization_id)
)

Note - The above two SQL queries are pulling up the Department, Grade, Job information but the same logic can be extended to fetch any of the other relevant information with similar criteria.

Try this out, and play around!