WLF_CM_PERSONS_COUNT_V

Details

• Schema: FUSION

• Object owner: WLF

• Object type: VIEW

Columns

Name
MANAGER_ID ORG_COUNT DIRECTS_COUNT CHANGE_DATE EFFECTIVE_START_DATE EFFECTIVE_END_DATE

Query

SQL_Statement

WITH EFFECTIVE_CHANGE_DATES AS (SELECT DISTINCT MANAGER_ID, EFFECTIVE_START_DATE AS CHANGE_DATE FROM PER_MANAGER_HRCHY_DN UNION ALL SELECT DISTINCT MANAGER_ID, EFFECTIVE_END_DATE AS CHANGE_DATE FROM PER_MANAGER_HRCHY_DN ) SELECT HRCHY.MANAGER_ID, COUNT(DISTINCT HRCHY.PERSON_ID) AS ORG_COUNT, SUM((CASE WHEN (HRCHY.MANAGER_LEVEL = 1 AND HRCHY.MANAGER_ID = CHANGE_DATES.MANAGER_ID) THEN 1 ELSE 0 END)) AS DIRECTS_COUNT, CHANGE_DATES.CHANGE_DATE, CHANGE_DATES.CHANGE_DATE AS EFFECTIVE_START_DATE, (SELECT MIN(CH.CHANGE_DATE)-1 FROM EFFECTIVE_CHANGE_DATES CH WHERE CHANGE_DATES.CHANGE_DATE < CH.CHANGE_DATE AND HRCHY.MANAGER_ID = CH.MANAGER_ID) AS EFFECTIVE_END_DATE FROM PER_MANAGER_HRCHY_DN HRCHY, EFFECTIVE_CHANGE_DATES CHANGE_DATES WHERE HRCHY.MANAGER_ID = CHANGE_DATES.MANAGER_ID AND TRUNC(CHANGE_DATES.CHANGE_DATE) BETWEEN HRCHY.EFFECTIVE_START_DATE AND HRCHY.EFFECTIVE_END_DATE GROUP BY HRCHY.MANAGER_ID, CHANGE_DATES.CHANGE_DATE ORDER BY CHANGE_DATES.CHANGE_DATE