In this article, we will look into a query using which we can find the worker count by Person Type.
If you don’t know about Person Types, check the importance of Person Type here.
SELECT TYP.USER_PERSON_TYPE,
TYP.SYSTEM_PERSON_TYPE,
COUNT(*) PERSON_COUNT
FROM PER_ALL_PEOPLE_F PER,
PER_PERSON_TYPE_USAGES_F USAG,
PER_PERSON_TYPES_VL TYP
WHERE SYSDATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN USAG.EFFECTIVE_START_DATE
AND USAG.EFFECTIVE_END_DATE
AND USAG.PERSON_ID = PER.PERSON_ID
AND TYP.PERSON_TYPE_ID = USAG.PERSON_TYPE_ID
GROUP BY TYP.USER_PERSON_TYPE,
TYP.SYSTEM_PERSON_TYPE
ORDER BY SYSTEM_PERSON_TYPE,
USER_PERSON_TYPE
The output will look like below based on the Person Types configured in the instance.
