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((EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME,
COUNT((EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_COUNT,
SUM(EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_VAL,
COUNT(EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24)EQUIP_CAL_TIME_VAL_COUNT,
SUM(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN (EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME,
COUNT(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN (EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_COUNT,
SUM(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_VAL,
COUNT(CASE WHEN EQ_SHIFTS.AVAILABILITY_FLAG='Y' THEN EQ_SHIFTS.RESOURCE_COST*(EQ_SHIFTS.TO_DATE-EQ_SHIFTS.FROM_DATE)*24 ELSE 0 END)EQUIP_SCH_TIME_VAL_COUNT,
COUNT(*)TOTAL_COUNT
FROM
MTH_EQUIPMENT_SHIFTS_D EQ_SHIFTS,
MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D,
MTH_EQUIPMENT_DENORM_D DEPT_HIER,
MTH_EQUIPMENT_DENORM_D CUST_HIER
WHERE
EQ_SHIFTS.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY
AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2
AND DEPT_HIER.EQUIPMENT_FK_KEY = EQ_SHIFTS.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_SHIFTS.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]