SELECT *
FROM ( SELECT hro.name department,
COUNT (Employee_number) Total_emp_hire,
TO_CHAR (start_date, 'MM') mon
FROM per_all_people_f pap,
per_all_assignments_f paf,
hr_all_organization_units hro
WHERE TO_CHAR (start_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')
AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND pap.person_id = paf.person_id
AND paf.organization_id = hro.organization_id
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
AND paf.effective_end_date
GROUP BY TO_CHAR (start_date, 'MM'),hro.name
) PIVOT (SUM (
NVL (Total_emp_hire,
0))
FOR Mon
IN ('01' AS JAN,
'02' AS FEB,
'03' AS MAR,
'04' AS APR,
'05' AS MAY,
'06' AS JUN,
'07' AS JUL,
'08' AS AUG,
'09' AS SEP,
'10' AS OCT,
'11' AS NOV,
'12' AS DEC))
order by 1, 2
FROM ( SELECT hro.name department,
COUNT (Employee_number) Total_emp_hire,
TO_CHAR (start_date, 'MM') mon
FROM per_all_people_f pap,
per_all_assignments_f paf,
hr_all_organization_units hro
WHERE TO_CHAR (start_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')
AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND pap.person_id = paf.person_id
AND paf.organization_id = hro.organization_id
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
AND paf.effective_end_date
GROUP BY TO_CHAR (start_date, 'MM'),hro.name
) PIVOT (SUM (
NVL (Total_emp_hire,
0))
FOR Mon
IN ('01' AS JAN,
'02' AS FEB,
'03' AS MAR,
'04' AS APR,
'05' AS MAY,
'06' AS JUN,
'07' AS JUL,
'08' AS AUG,
'09' AS SEP,
'10' AS OCT,
'11' AS NOV,
'12' AS DEC))
order by 1, 2
No comments:
Post a Comment