SELECT
TIME_D.DAY_ID,
TIME_D.CALENDAR_MONTH_ID,
DEPT_HIER.LEVEL8_LEVEL_KEY DEPT_KEY,
DEPT_HIER.LEVEL8_EFFECTIVE_DATE DEPT_EFF_DATE,
CUST_HIER.LEVEL9_LEVEL_KEY CUST_LEVEL9_KEY,
CUST_HIER.LEVEL9_EFFECTIVE_DATE CUST_LEVEL9_EFF_DATE,
SUM(DOWN_HOURS)DOWN_HOURS,
COUNT(DOWN_HOURS)DOWN_HOURS_COUNT,
SUM(RESOURCE_COST*DOWN_HOURS)DOWN_HOURS_VAL,
COUNT(RESOURCE_COST*DOWN_HOURS)DOWN_HOURS_VAL_COUNT,
SUM(RUN_HOURS)RUN_HOURS,
COUNT(RUN_HOURS)RUN_HOURS_COUNT,
SUM(RESOURCE_COST*RUN_HOURS)RUN_HOURS_VAL,
COUNT(RESOURCE_COST*RUN_HOURS)RUN_HOURS_VAL_COUNT,
SUM(IDLE_HOURS)IDLE_HOURS,
COUNT(IDLE_HOURS)IDLE_HOURS_COUNT,
SUM(UP_HOURS)UP_HOURS,
COUNT(UP_HOURS)UP_HOURS_COUNT,
SUM(REQUIRED_HOURS/(CASE WHEN RUN_HOURS = 0 THEN NULL ELSE RUN_HOURS END))MAC_EFF_SUM,
COUNT(REQUIRED_HOURS/(CASE WHEN RUN_HOURS = 0 THEN NULL ELSE RUN_HOURS END))MAC_EFF_COUNT,
SUM(WO_ITEM_COUNT)WO_ITEM_SUM,
COUNT(WO_ITEM_COUNT)WO_ITEM_COUNT,
SUM(CASE WHEN (RUN_HOURS-REQUIRED_HOURS) < 0 THEN 0 ELSE RESOURCE_COST*(RUN_HOURS-REQUIRED_HOURS)END)LOSS_INEFF,
COUNT(CASE WHEN (RUN_HOURS-REQUIRED_HOURS) < 0 THEN 0 ELSE RESOURCE_COST*(RUN_HOURS-REQUIRED_HOURS)END)LOSS_INEFF_COUNT,
COUNT(*)TOTAL_COUNT
FROM
MTH_EQUIP_STATUS_SUMMARY EQ_STATUS,
MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D,
MTH_EQUIPMENT_DENORM_D DEPT_HIER,
MTH_EQUIPMENT_DENORM_D CUST_HIER
WHERE
EQ_STATUS.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY
AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2
AND DEPT_HIER.EQUIPMENT_FK_KEY = EQ_STATUS.EQUIPMENT_FK_KEY
AND TIME_D.FROM_DATE BETWEEN DEPT_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(DEPT_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY'))
AND CUST_HIER.EQUIPMENT_HIERARCHY_KEY = -3
AND CUST_HIER.EQUIPMENT_FK_KEY = EQ_STATUS.EQUIPMENT_FK_KEY
AND TIME_D.FROM_DATE BETWEEN CUST_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(CUST_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY'))
GROUP BY DEPT_HIER.LEVEL8_LEVEL_KEY, DEPT_HIER.LEVEL8_EFFECTIVE_DATE, TIME_D.DAY_ID,TIME_D.CALENDAR_MONTH_ID,CUST_HIER.LEVEL9_LEVEL_KEY,CUST_HIER.LEVEL9_EFFECTIVE_DATE
[Home] [Help]