SELECT "A3"."DAY_ID" "DAY_ID","A3"."CALENDAR_MONTH_ID" "CALENDAR_MONTH_ID","A2"."LEVEL8_LEVEL_KEY" "DEPT_KEY","A2"."LEVEL8_EFFECTIVE_DATE" "DEPT_EFF_DATE","A1"."LEVEL9_LEVEL_KEY" "CUST_LEVEL9_KEY","A1"."LEVEL9_EFFECTIVE_DATE" "CUST_LEVEL9_EFF_DATE",SUM(("A4"."TO_DATE"-"A4"."FROM_DATE")*24) "EQUIP_CAL_TIME",COUNT(("A4"."TO_DATE"-"A4"."FROM_DATE")*24) "EQUIP_CAL_TIME_COUNT",SUM("A4"."RESOURCE_COST"*("A4"."TO_DATE"-"A4"."FROM_DATE")*24) "EQUIP_CAL_TIME_VAL",COUNT("A4"."RESOURCE_COST"*("A4"."TO_DATE"-"A4"."FROM_DATE")*24) "EQUIP_CAL_TIME_VAL_COUNT",SUM(CASE WHEN "A4"."AVAILABILITY_FLAG"='Y' THEN ("A4"."TO_DATE"-"A4"."FROM_DATE")*24 ELSE 0 END ) "EQUIP_SCH_TIME",COUNT(CASE WHEN "A4"."AVAILABILITY_FLAG"='Y' THEN ("A4"."TO_DATE"-"A4"."FROM_DATE")*24 ELSE 0 END ) "EQUIP_SCH_TIME_COUNT",SUM(CASE WHEN "A4"."AVAILABILITY_FLAG"='Y' THEN "A4"."RESOURCE_COST"*("A4"."TO_DATE"-"A4"."FROM_DATE")*24 ELSE 0 END ) "EQUIP_SCH_TIME_VAL",COUNT(CASE WHEN "A4"."AVAILABILITY_FLAG"='Y' THEN "A4"."RESOURCE_COST"*("A4"."TO_DATE"-"A4"."FROM_DATE")*24 ELSE 0 END ) "EQUIP_SCH_TIME_VAL_COUNT",COUNT(*) "TOTAL_COUNT" FROM "MTH"."MTH_EQUIPMENT_SHIFTS_D" "A4","APPS"."MTH_SHIFT_GREGORIAN_DENORM_MV" "A3","MTH"."MTH_EQUIPMENT_DENORM_D" "A2","MTH"."MTH_EQUIPMENT_DENORM_D" "A1" WHERE "A4"."ENTITY_TYPE"='EQUIPMENT' AND "A4"."SHIFT_WORKDAY_FK_KEY"="A3"."SHIFT_WORKDAY_PK_KEY" AND "A2"."EQUIPMENT_HIERARCHY_KEY"=(-2) AND "A2"."EQUIPMENT_FK_KEY"="A4"."EQUIPMENT_FK_KEY" AND "A3"."FROM_DATE">="A2"."EQUIPMENT_EFFECTIVE_DATE" AND "A3"."FROM_DATE"<=NVL("A2"."EQUIPMENT_EXPIRATION_DATE",TO_DATE('01/01/2100','MM/DD/YYYY')) AND "A1"."EQUIPMENT_HIERARCHY_KEY"=(-3) AND "A1"."EQUIPMENT_FK_KEY"="A4"."EQUIPMENT_FK_KEY" AND "A3"."FROM_DATE">="A1"."EQUIPMENT_EFFECTIVE_DATE" AND "A3"."FROM_DATE"<=NVL("A1"."EQUIPMENT_EXPIRATION_DATE",TO_DATE('01/01/2100','MM/DD/YYYY')) GROUP BY "A2"."LEVEL8_LEVEL_KEY","A2"."LEVEL8_EFFECTIVE_DATE","A3"."DAY_ID","A3"."CALENDAR_MONTH_ID","A1"."LEVEL9_LEVEL_KEY","A1"."LEVEL9_EFFECTIVE_DATE"
[Home] [Help]