SELECT
DAY_ID,
CALENDAR_MONTH_ID,
DEPT_KEY,
DEPT_EFF_DATE,
CUST_LEVEL9_KEY,
CUST_LEVEL9_EFF_DATE,
SUM(EQUIP_CAL_TIME)EQUIP_CAL_TIME,
COUNT(EQUIP_CAL_TIME)EQUIP_CAL_TIME_COUNT,
SUM(EQUIP_CAL_TIME_VAL)EQUIP_CAL_TIME_VAL,
COUNT(EQUIP_CAL_TIME_VAL)EQUIP_CAL_TIME_VAL_COUNT,
SUM(EQUIP_SCH_TIME)EQUIP_SCH_TIME,
COUNT(EQUIP_SCH_TIME)EQUIP_SCH_TIME_COUNT,
SUM(EQUIP_SCH_TIME_VAL)EQUIP_SCH_TIME_VAL,
COUNT(EQUIP_SCH_TIME_VAL)EQUIP_SCH_TIME_VAL_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_SHFT_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(EQUIP_CAL_TIME)EQUIP_CAL_TIME
,
COUNT(EQUIP_CAL_TIME)EQUIP_CAL_TIME_COUNT
,
SUM(EQUIP_CAL_TIME_VAL)EQUIP_CAL_TIME_VAL
,
COUNT(EQUIP_CAL_TIME_VAL)EQUIP_CAL_TIME_VAL_COUNT
,
SUM(EQUIP_SCH_TIME)EQUIP_SCH_TIME
,
COUNT(EQUIP_SCH_TIME)EQUIP_SCH_TIME_COUNT
,
SUM(EQUIP_SCH_TIME_VAL)EQUIP_SCH_TIME_VAL
,
COUNT(EQUIP_SCH_TIME_VAL)EQUIP_SCH_TIME_VAL_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_SHFT_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))
|
|
|