SELECT
DAY_ID,
CALENDAR_MONTH_ID,
DEPT_KEY,
DEPT_EFF_DATE,
CUST_LEVEL9_KEY,
CUST_LEVEL9_EFF_DATE,
SUM(DOWN_HOURS)DOWN_HOURS,
COUNT(DOWN_HOURS)DOWN_HOURS_COUNT,
SUM(DOWN_HOURS_VAL)DOWN_HOURS_VAL,
COUNT(DOWN_HOURS_VAL)DOWN_HOURS_VAL_COUNT,
SUM(RUN_HOURS)RUN_HOURS,
COUNT(RUN_HOURS)RUN_HOURS_COUNT,
SUM(RUN_HOURS_VAL)RUN_HOURS_VAL,
COUNT(RUN_HOURS_VAL)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(MAC_EFF_SUM)MAC_EFF_SUM,
COUNT(MAC_EFF_SUM)MAC_EFF_COUNT,
SUM(WO_ITEM_SUM)WO_ITEM_SUM,
COUNT(WO_ITEM_SUM)WO_ITEM_COUNT,
SUM(LOSS_INEFF)LOSS_INEFF,
COUNT(LOSS_INEFF)LOSS_INEFF_COUNT,
COUNT(*)TOTAL_COUNT,
GROUPING_ID(DAY_ID,CALENDAR_MONTH_ID,DEPT_KEY,DEPT_EFF_DATE,CUST_LEVEL9_KEY,CUST_LEVEL9_EFF_DATE)GROUPING_ID,
DECODE (GROUPING_ID(DAY_ID, CALENDAR_MONTH_ID),
1,DAY_ID,
2,CALENDAR_MONTH_ID)TIME_DIM_KEY,
DECODE (GROUPING_ID(DAY_ID, CALENDAR_MONTH_ID),
1,10,
2,9)TIME_DIM_LEVEL,
DECODE (GROUPING_ID(DEPT_KEY, CUST_LEVEL9_KEY),
1,-2,
2,-3)EQUIP_HIER,
DECODE (GROUPING_ID(DEPT_KEY, CUST_LEVEL9_KEY),
1,DEPT_KEY,
2,CUST_LEVEL9_KEY)EQUIP_HIER_KEY,
DECODE (GROUPING_ID(DEPT_EFF_DATE, CUST_LEVEL9_EFF_DATE),
1,DEPT_EFF_DATE,
2,CUST_LEVEL9_EFF_DATE)EQUIP_HIER_EFF_DATE
FROM
MTH_EQUIP_ST_SUM_DD_MV
GROUP BY GROUPING SETS ((DEPT_KEY,CALENDAR_MONTH_ID,DEPT_EFF_DATE),
(CUST_LEVEL9_KEY,DAY_ID,CUST_LEVEL9_EFF_DATE),
(CUST_LEVEL9_KEY,CALENDAR_MONTH_ID,CUST_LEVEL9_EFF_DATE))
SELECT
DAY_ID
,
CALENDAR_MONTH_ID
,
DEPT_KEY
,
DEPT_EFF_DATE
,
CUST_LEVEL9_KEY
,
CUST_LEVEL9_EFF_DATE
,
SUM(DOWN_HOURS)DOWN_HOURS
,
COUNT(DOWN_HOURS)DOWN_HOURS_COUNT
,
SUM(DOWN_HOURS_VAL)DOWN_HOURS_VAL
,
COUNT(DOWN_HOURS_VAL)DOWN_HOURS_VAL_COUNT
,
SUM(RUN_HOURS)RUN_HOURS
,
COUNT(RUN_HOURS)RUN_HOURS_COUNT
,
SUM(RUN_HOURS_VAL)RUN_HOURS_VAL
,
COUNT(RUN_HOURS_VAL)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(MAC_EFF_SUM)MAC_EFF_SUM
,
COUNT(MAC_EFF_SUM)MAC_EFF_COUNT
,
SUM(WO_ITEM_SUM)WO_ITEM_SUM
,
COUNT(WO_ITEM_SUM)WO_ITEM_COUNT
,
SUM(LOSS_INEFF)LOSS_INEFF
,
COUNT(LOSS_INEFF)LOSS_INEFF_COUNT
,
COUNT(*)TOTAL_COUNT
,
GROUPING_ID(DAY_ID
, CALENDAR_MONTH_ID
, DEPT_KEY
, DEPT_EFF_DATE
, CUST_LEVEL9_KEY
, CUST_LEVEL9_EFF_DATE)GROUPING_ID
,
DECODE (GROUPING_ID(DAY_ID
, CALENDAR_MONTH_ID)
,
1
, DAY_ID
,
2
, CALENDAR_MONTH_ID)TIME_DIM_KEY
,
DECODE (GROUPING_ID(DAY_ID
, CALENDAR_MONTH_ID)
,
1
, 10
,
2
, 9)TIME_DIM_LEVEL
,
DECODE (GROUPING_ID(DEPT_KEY
, CUST_LEVEL9_KEY)
,
1
, -2
,
2
, -3)EQUIP_HIER
,
DECODE (GROUPING_ID(DEPT_KEY
, CUST_LEVEL9_KEY)
,
1
, DEPT_KEY
,
2
, CUST_LEVEL9_KEY)EQUIP_HIER_KEY
,
DECODE (GROUPING_ID(DEPT_EFF_DATE
, CUST_LEVEL9_EFF_DATE)
,
1
, DEPT_EFF_DATE
,
2
, CUST_LEVEL9_EFF_DATE)EQUIP_HIER_EFF_DATE
FROM
MTH_EQUIP_ST_SUM_DD_MV
GROUP BY GROUPING SETS ((DEPT_KEY
, CALENDAR_MONTH_ID
, DEPT_EFF_DATE)
,
(CUST_LEVEL9_KEY
, DAY_ID
, CUST_LEVEL9_EFF_DATE)
,
(CUST_LEVEL9_KEY
, CALENDAR_MONTH_ID
, CUST_LEVEL9_EFF_DATE))
|
|
|