SELECT
DAY_ID,
CALENDAR_MONTH_ID,
STATUS_CODE,
PRODUCT_LEVEL9_KEY,
DEPT_KEY,
DEPT_EFF_DATE,
CUST_LEVEL9_KEY,
CUST_LEVEL9_EFF_DATE,
SUM(REJECTED_QTY)REJECTED_QTY,
COUNT(REJECTED_QTY)REJECTED_QTY_COUNT,
SUM(QTY_REWORK)QTY_REWORK,
COUNT(QTY_REWORK)QTY_REWORK_COUNT,
SUM(QTY_SCRAP)QTY_SCRAP,
COUNT(QTY_SCRAP)QTY_SCRAP_COUNT,
SUM(QTY_GOOD)QTY_GOOD,
COUNT(QTY_GOOD)QTY_GOOD_COUNT,
SUM(OUTPUT_QTY)OUTPUT_QTY,
COUNT(OUTPUT_QTY)OUTPUT_QTY_COUNT,
SUM(COMPLETED_QTY)COMPLETED_QTY,
COUNT(COMPLETED_QTY)COMPLETED_QTY_COUNT,
SUM(HRS_ACT_COMP_QTY)HRS_ACT_COMP_QTY,
COUNT(HRS_ACT_COMP_QTY)HRS_ACT_COMP_QTY_COUNT,
SUM(SHIFT_ACT_PROD)SHIFT_ACT_PROD,
COUNT(SHIFT_ACT_PROD)SHIFT_ACT_PROD_COUNT,
SUM(LOSS_BAD_QUALITY)LOSS_BAD_QUALITY,
COUNT(LOSS_BAD_QUALITY)LOSS_BAD_QUALITY_COUNT,
SUM(LOSS_BAD_QUALTY_VAL)LOSS_BAD_QUALTY_VAL,
COUNT(LOSS_BAD_QUALTY_VAL)LOSS_BAD_QUALTY_VAL_COUNT,
COUNT(*)TOTAL_COUNT,
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_OP_SUM_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
,
STATUS_CODE
,
PRODUCT_LEVEL9_KEY
,
DEPT_KEY
,
DEPT_EFF_DATE
,
CUST_LEVEL9_KEY
,
CUST_LEVEL9_EFF_DATE
,
SUM(REJECTED_QTY)REJECTED_QTY
,
COUNT(REJECTED_QTY)REJECTED_QTY_COUNT
,
SUM(QTY_REWORK)QTY_REWORK
,
COUNT(QTY_REWORK)QTY_REWORK_COUNT
,
SUM(QTY_SCRAP)QTY_SCRAP
,
COUNT(QTY_SCRAP)QTY_SCRAP_COUNT
,
SUM(QTY_GOOD)QTY_GOOD
,
COUNT(QTY_GOOD)QTY_GOOD_COUNT
,
SUM(OUTPUT_QTY)OUTPUT_QTY
,
COUNT(OUTPUT_QTY)OUTPUT_QTY_COUNT
,
SUM(COMPLETED_QTY)COMPLETED_QTY
,
COUNT(COMPLETED_QTY)COMPLETED_QTY_COUNT
,
SUM(HRS_ACT_COMP_QTY)HRS_ACT_COMP_QTY
,
COUNT(HRS_ACT_COMP_QTY)HRS_ACT_COMP_QTY_COUNT
,
SUM(SHIFT_ACT_PROD)SHIFT_ACT_PROD
,
COUNT(SHIFT_ACT_PROD)SHIFT_ACT_PROD_COUNT
,
SUM(LOSS_BAD_QUALITY)LOSS_BAD_QUALITY
,
COUNT(LOSS_BAD_QUALITY)LOSS_BAD_QUALITY_COUNT
,
SUM(LOSS_BAD_QUALTY_VAL)LOSS_BAD_QUALTY_VAL
,
COUNT(LOSS_BAD_QUALTY_VAL)LOSS_BAD_QUALTY_VAL_COUNT
,
COUNT(*)TOTAL_COUNT
,
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_OP_SUM_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))
|
|
|