This Employee Age Analysis query displays the distribution of employees by specific age groups across different organizations as of today, helping users to analyze workforce demographics for better organizational planning and human resource management.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve employees by age groups across organizations as of today.
HAOU.NAME ORGANIZATION,
PPF.PERSON_NUMBER EMPLOYEE_NUMBER,
PPNF.FULL_NAME EMPLOYEE_NAME,
PPF.DATE_OF_BIRTH,
-- Age groups (youngest to oldest)
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
1, 1, 2, 1, 3, 1, 4, 1, 5, 1,
6, 1, 7, 1, 8, 1, 9, 1, 10, 1,
11, 1, 12, 1, 13, 1, 14, 1, 15, 1,
16, 1, 17, 1, 18, 1, 19, 1, 0) AGE_LESS_THAN_20,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
20, 1, 21, 1, 22, 1, 23, 1, 24, 1, 25, 1, 0) AGE_20_25,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
26, 1, 27, 1, 28, 1, 29, 1, 30, 1, 0) AGE_26_30,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
31, 1, 32, 1, 33, 1, 34, 1, 35, 1, 0) AGE_31_35,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
36, 1, 37, 1, 38, 1, 39, 1, 40, 1, 0) AGE_36_40,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
41, 1, 42, 1, 43, 1, 44, 1, 45, 1, 0) AGE_41_45,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
46, 1, 47, 1, 48, 1, 49, 1, 50, 1, 0) AGE_46_50,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
51, 1, 52, 1, 53, 1, 54, 1, 55, 1, 0) AGE_51_55,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
56, 1, 57, 1, 58, 1, 59, 1, 60, 1, 0) AGE_56_60,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
61, 1, 62, 1, 63, 1, 64, 1, 65, 1, 0) AGE_61_65,
DECODE (
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12),
66, 1, 67, 1, 68, 1, 69, 1, 70, 1, 0) AGE_66_70,
DECODE (
SIGN(MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH) - (71 * 12)),
1, 1, 0, 1, 0) AGE_MORE_THAN_70,
-- Age unknown
DECODE(PPF.DATE_OF_BIRTH, NULL, 1, 0) AGE_UNKNOWN,
-- Exact age
DECODE (
PPF.DATE_OF_BIRTH,
NULL, 0,
TRUNC((MONTHS_BETWEEN(DT.TODAY, PPF.DATE_OF_BIRTH)) / 12)
) AGE_IN_YEARS
FROM
PER_PEOPLE_F PPF,
PER_PERSON_NAMES_F PPNF,
PER_ALL_ASSIGNMENTS_M PAAM,
HR_ALL_ORGANIZATION_UNITS HAOU,
(SELECT SYSDATE TODAY FROM DUAL) DT
WHERE 1=1
AND PPF.PERSON_ID = PAAM.PERSON_ID
AND PPF.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PAAM.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND TRUNC (SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE
AND PAAM.EFFECTIVE_END_DATE