SELECT
DAY_ID,
CALENDAR_MONTH_ID,
SUM(PLANNED_QTY_G)PLANNED_QTY_G,
COUNT(PLANNED_QTY_G)PLANNED_QTY_G_COUNT,
SUM(PLANNED_QTY_SG)PLANNED_QTY_SG,
COUNT(PLANNED_QTY_SG)PLANNED_QTY_SG_COUNT,
SUM(SHIFT_SCH_PROD)SHIFT_SCH_PROD,
COUNT(SHIFT_SCH_PROD)SHIFT_SCH_PROD_COUNT,
SUM(HRS_SCH_QTY)HRS_SCH_QTY,
COUNT(HRS_SCH_QTY)HRS_SCH_QTY_COUNT,
COUNT(*) TOTAL_COUNT,
STATUS_CODE,
PRODUCT_LEVEL9_KEY,
DEPT_KEY,
DEPT_EFF_DATE,
CUST_LEVEL9_KEY,
CUST_LEVEL9_EFF_DATE,
GROUPING_ID (DAY_ID,CALENDAR_MONTH_ID,STATUS_CODE,PRODUCT_LEVEL9_KEY,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_PROD_SCH_DD_MV
GROUP BY GROUPING SETS ((DEPT_KEY,CALENDAR_MONTH_ID,PRODUCT_LEVEL9_KEY,DEPT_EFF_DATE,STATUS_CODE),
(CUST_LEVEL9_KEY,DAY_ID,PRODUCT_LEVEL9_KEY,CUST_LEVEL9_EFF_DATE,STATUS_CODE),
(CUST_LEVEL9_KEY,CALENDAR_MONTH_ID,PRODUCT_LEVEL9_KEY,CUST_LEVEL9_EFF_DATE,STATUS_CODE))
SELECT
DAY_ID
,
CALENDAR_MONTH_ID
,
SUM(PLANNED_QTY_G)PLANNED_QTY_G
,
COUNT(PLANNED_QTY_G)PLANNED_QTY_G_COUNT
,
SUM(PLANNED_QTY_SG)PLANNED_QTY_SG
,
COUNT(PLANNED_QTY_SG)PLANNED_QTY_SG_COUNT
,
SUM(SHIFT_SCH_PROD)SHIFT_SCH_PROD
,
COUNT(SHIFT_SCH_PROD)SHIFT_SCH_PROD_COUNT
,
SUM(HRS_SCH_QTY)HRS_SCH_QTY
,
COUNT(HRS_SCH_QTY)HRS_SCH_QTY_COUNT
,
COUNT(*) TOTAL_COUNT
,
STATUS_CODE
,
PRODUCT_LEVEL9_KEY
,
DEPT_KEY
,
DEPT_EFF_DATE
,
CUST_LEVEL9_KEY
,
CUST_LEVEL9_EFF_DATE
,
GROUPING_ID (DAY_ID
, CALENDAR_MONTH_ID
, STATUS_CODE
, PRODUCT_LEVEL9_KEY
, 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_PROD_SCH_DD_MV
GROUP BY GROUPING SETS ((DEPT_KEY
, CALENDAR_MONTH_ID
, PRODUCT_LEVEL9_KEY
, DEPT_EFF_DATE
, STATUS_CODE)
,
(CUST_LEVEL9_KEY
, DAY_ID
, PRODUCT_LEVEL9_KEY
, CUST_LEVEL9_EFF_DATE
, STATUS_CODE)
,
(CUST_LEVEL9_KEY
, CALENDAR_MONTH_ID
, PRODUCT_LEVEL9_KEY
, CUST_LEVEL9_EFF_DATE
, STATUS_CODE))
|
|
|