안녕하세요.
피봇과 관련되어 질문을 드립니다.
CO_SO 정보는 Order 주문입니다.
CO_LINE_HOL 정보는 휴무 정보입니다.
- HOL_TYP 값 무시
- START_DT_TIME, END_DT_TIME 이 휴무 시작일과 종료일
CO_LINE_CAPA 는 라인별 캐파 정보 입니다.
CO_CALD_MST 는 캘린더 정보 입니다.
아래와 같은 결과를 얻고 싶습니다.
-- 데이터 WITH CO_SO AS ( SELECT 'ITEM_0001' AS ITEM_CD, '24000' AS SO_QTY, 1 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0002' AS ITEM_CD, '26000' AS SO_QTY, 2 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0003' AS ITEM_CD, '25000' AS SO_QTY, 3 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0010' AS ITEM_CD, '15000' AS SO_QTY, 4 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0011' AS ITEM_CD, '40000' AS SO_QTY, 5 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0012' AS ITEM_CD, '25000' AS SO_QTY, 6 AS PRIORITY FROM DUAL ) , CO_LINE_HOL AS ( SELECT 'ALL', 'REGULAR', '2017-07-24 8:00', '2017-07-31 8:00', '휴가' FROM DUAL UNION ALL SELECT 'L1', 'PM', '2017-07-18 8:00', '2017-07-19 8:00', '' FROM DUAL UNION ALL SELECT 'L2', 'PM', '2017-08-02 8:00', '2017-08-04 8:00', '' FROM DUAL ) , CO_LINE_CAPA AS ( SELECT 'L1', 'ITEM_0001', '3000 ' FROM DUAL UNION ALL SELECT 'L1', 'ITEM_0002', '4000 ' FROM DUAL UNION ALL SELECT 'L1', 'ITEM_0003', '5000 ' FROM DUAL UNION ALL SELECT 'L2', 'ITEM_0010', '3000 ' FROM DUAL UNION ALL SELECT 'L2', 'ITEM_0011', '4000 ' FROM DUAL UNION ALL SELECT 'L2', 'ITEM_0012', '5000 ' FROM DUAL ) , CO_CALD_MAT AS ( SELECT '20170717', '201729' FROM DUAL UNION ALL SELECT '20170718', '201729' FROM DUAL UNION ALL SELECT '20170719', '201729' FROM DUAL UNION ALL SELECT '20170720', '201729' FROM DUAL UNION ALL SELECT '20170721', '201729' FROM DUAL UNION ALL SELECT '20170722', '201729' FROM DUAL UNION ALL SELECT '20170723', '201729' FROM DUAL UNION ALL SELECT '20170724', '201730' FROM DUAL UNION ALL SELECT '20170725', '201730' FROM DUAL UNION ALL SELECT '20170726', '201730' FROM DUAL UNION ALL SELECT '20170727', '201730' FROM DUAL UNION ALL SELECT '20170728', '201730' FROM DUAL UNION ALL SELECT '20170729', '201730' FROM DUAL UNION ALL SELECT '20170730', '201730' FROM DUAL UNION ALL SELECT '20170731', '201731A' FROM DUAL UNION ALL SELECT '20170801', '201731B' FROM DUAL UNION ALL SELECT '20170802', '201731B' FROM DUAL UNION ALL SELECT '20170803', '201731B' FROM DUAL UNION ALL SELECT '20170804', '201731B' FROM DUAL UNION ALL SELECT '20170805', '201731B' FROM DUAL UNION ALL SELECT '20170806', '201731B' FROM DUAL UNION ALL SELECT '20170807', '201732' FROM DUAL UNION ALL SELECT '20170808', '201732' FROM DUAL UNION ALL SELECT '20170809', '201732' FROM DUAL UNION ALL SELECT '20170810', '201732' FROM DUAL UNION ALL SELECT '20170811', '201732' FROM DUAL UNION ALL SELECT '20170812', '201732' FROM DUAL UNION ALL SELECT '20170813', '201732' FROM DUAL )
주차를 PIVOT 으로 펼치는 기능이 있어야 할 것 같은데..
어렵네요.
감사합니다.
피벗하기 전 일자별 결과물을 보여주세요.
어떤 형태로 수량 배분이 되는지?
특히 ITEM_0002 의 경우 4000 씩 쪼개다 보면 CAPA 4000 중에 절반인 2000 이 남게 되는데.
다음 ITEM_0003 처리시 동일일자로 나머지 절반을 사용해야 하는지? 5000 의 절반 2500.
아니면 남은 capa 무시하고 일자를 바꾸어 새로 시작해야 하는지? 5000.
아이템 10,11,12 는 보여주신 결과가 맞는지? 순서가 이상한 듯 하구요.
지난번 질문에서는 라인도 2개 아이템도 2개 있었고 2*2 조합의 line-kapa 을 모두 가지고 있었는데.
이번 질문에는 아이템 하나당 하나의 라인만 지정되어 있네요? 이게 맞는건가요? 다른 조합도 가능한지?
마농님 안녕하세요.
저번 질문에 오류가 있어 다시 정리하여 질문 드립니다.
* 수정사항
- SO 의 수량과 우선순위에 오차가 있었습니다.
- PIVOT 을 돌리기 전 데이터를 첨부하였습니다.
* 물량 배분 룰
1. CAPA 가 남아도 다음 ITEM 처리 시 동일일자로 나머지 일자를 사용하지 않는다.
남은 capa 무시하고 일자 바꾸어 새로 시작한다.
2. 하나의 item 은 하나의 line 에서만 생산 가능하다. (다른 조합 불가능)
-- 데이터 WITH CO_SO AS ( SELECT 'ITEM_0001' AS ITEM_CD, '24000' AS SO_QTY, 1 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0002' AS ITEM_CD, '26000' AS SO_QTY, 2 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0003' AS ITEM_CD, '25000' AS SO_QTY, 3 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0010' AS ITEM_CD, '15000' AS SO_QTY, 6 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0011' AS ITEM_CD, '40000' AS SO_QTY, 4 AS PRIORITY FROM DUAL UNION ALL SELECT 'ITEM_0012' AS ITEM_CD, '20000' AS SO_QTY, 5 AS PRIORITY FROM DUAL ) , CO_LINE_HOL AS ( SELECT 'ALL', 'REGULAR', '2017-07-24 8:00', '2017-07-31 8:00', '휴가' FROM DUAL UNION ALL SELECT 'L1', 'PM', '2017-07-18 8:00', '2017-07-19 8:00', '' FROM DUAL UNION ALL SELECT 'L2', 'PM', '2017-08-02 8:00', '2017-08-04 8:00', '' FROM DUAL ) , CO_LINE_CAPA AS ( SELECT 'L1', 'ITEM_0001', '3000 ' FROM DUAL UNION ALL SELECT 'L1', 'ITEM_0002', '4000 ' FROM DUAL UNION ALL SELECT 'L1', 'ITEM_0003', '5000 ' FROM DUAL UNION ALL SELECT 'L2', 'ITEM_0010', '3000 ' FROM DUAL UNION ALL SELECT 'L2', 'ITEM_0011', '4000 ' FROM DUAL UNION ALL SELECT 'L2', 'ITEM_0012', '5000 ' FROM DUAL ) , CO_CALD_MAT AS ( SELECT '20170717', '201729' FROM DUAL UNION ALL SELECT '20170718', '201729' FROM DUAL UNION ALL SELECT '20170719', '201729' FROM DUAL UNION ALL SELECT '20170720', '201729' FROM DUAL UNION ALL SELECT '20170721', '201729' FROM DUAL UNION ALL SELECT '20170722', '201729' FROM DUAL UNION ALL SELECT '20170723', '201729' FROM DUAL UNION ALL SELECT '20170724', '201730' FROM DUAL UNION ALL SELECT '20170725', '201730' FROM DUAL UNION ALL SELECT '20170726', '201730' FROM DUAL UNION ALL SELECT '20170727', '201730' FROM DUAL UNION ALL SELECT '20170728', '201730' FROM DUAL UNION ALL SELECT '20170729', '201730' FROM DUAL UNION ALL SELECT '20170730', '201730' FROM DUAL UNION ALL SELECT '20170731', '201731A' FROM DUAL UNION ALL SELECT '20170801', '201731B' FROM DUAL UNION ALL SELECT '20170802', '201731B' FROM DUAL UNION ALL SELECT '20170803', '201731B' FROM DUAL UNION ALL SELECT '20170804', '201731B' FROM DUAL UNION ALL SELECT '20170805', '201731B' FROM DUAL UNION ALL SELECT '20170806', '201731B' FROM DUAL UNION ALL SELECT '20170807', '201732' FROM DUAL UNION ALL SELECT '20170808', '201732' FROM DUAL UNION ALL SELECT '20170809', '201732' FROM DUAL UNION ALL SELECT '20170810', '201732' FROM DUAL UNION ALL SELECT '20170811', '201732' FROM DUAL UNION ALL SELECT '20170812', '201732' FROM DUAL UNION ALL SELECT '20170813', '201732' FROM DUAL )
<피봇을 돌리기 전 데이터> | |||
LINE_CD | ITEM_CD | CALD_DT | CAPA_QTY |
L1 | ITEM_0001 | 20170717 | 3000 |
L1 | ITEM_0001 | 20170718 | 0 |
L1 | ITEM_0001 | 20170719 | 3000 |
L1 | ITEM_0001 | 20170720 | 3000 |
L1 | ITEM_0001 | 20170721 | 3000 |
L1 | ITEM_0001 | 20170722 | 3000 |
L1 | ITEM_0001 | 20170723 | 3000 |
L1 | ITEM_0001 | 20170731 | 3000 |
L1 | ITEM_0001 | 20170801 | 3000 |
L1 | ITEM_0002 | 20170802 | 4000 |
L1 | ITEM_0002 | 20170803 | 4000 |
L1 | ITEM_0002 | 20170804 | 4000 |
L1 | ITEM_0002 | 20170805 | 4000 |
L1 | ITEM_0002 | 20170806 | 4000 |
L1 | ITEM_0002 | 20170807 | 4000 |
L1 | ITEM_0002 | 20170808 | 2000 |
L1 | ITEM_0003 | 20170809 | 5000 |
L1 | ITEM_0003 | 20170810 | 5000 |
L1 | ITEM_0003 | 20170811 | 5000 |
L1 | ITEM_0003 | 20170812 | 5000 |
L1 | ITEM_0003 | 20170813 | 5000 |
L2 | ITEM_0011 | 20170717 | 4000 |
L2 | ITEM_0011 | 20170718 | 4000 |
L2 | ITEM_0011 | 20170719 | 4000 |
L2 | ITEM_0011 | 20170720 | 4000 |
L2 | ITEM_0011 | 20170721 | 4000 |
L2 | ITEM_0011 | 20170722 | 4000 |
L2 | ITEM_0011 | 20170723 | 4000 |
L2 | ITEM_0011 | 20170731 | 4000 |
L2 | ITEM_0011 | 20170801 | 4000 |
L2 | ITEM_0011 | 20170802 | 0 |
L2 | ITEM_0011 | 20170803 | 0 |
L2 | ITEM_0011 | 20170804 | 4000 |
L2 | ITEM_0012 | 20170805 | 5000 |
L2 | ITEM_0012 | 20170806 | 5000 |
L2 | ITEM_0012 | 20170807 | 5000 |
L2 | ITEM_0012 | 20170808 | 5000 |
L2 | ITEM_0010 | 20170809 | 3000 |
L2 | ITEM_0010 | 20170810 | 3000 |
L2 | ITEM_0010 | 20170811 | 3000 |
L2 | ITEM_0010 | 20170812 | 3000 |
L2 | ITEM_0010 | 20170813 | 3000 |
WITH co_so AS ( SELECT 'ITEM_0001' item_cd, 24000 so_qty, 1 priority FROM dual UNION ALL SELECT 'ITEM_0002', 26000, 2 FROM dual UNION ALL SELECT 'ITEM_0003', 25000, 3 FROM dual UNION ALL SELECT 'ITEM_0010', 15000, 6 FROM dual UNION ALL SELECT 'ITEM_0011', 40000, 4 FROM dual UNION ALL SELECT 'ITEM_0012', 20000, 5 FROM dual ) , co_line_hol AS ( SELECT 'ALL' line_cd, 'REGULAR' hol_typ, '2017-07-24 8:00' start_dt_time, '2017-07-31 8:00' end_dt_time, '휴가' hol_txt FROM dual UNION ALL SELECT 'L1', 'PM', '2017-07-18 8:00', '2017-07-19 8:00', '' FROM dual UNION ALL SELECT 'L2', 'PM', '2017-08-02 8:00', '2017-08-04 8:00', '' FROM dual ) , co_line_capa AS ( SELECT 'L1' line_cd, 'ITEM_0001' item_cd, 3000 capa_qty FROM dual UNION ALL SELECT 'L1', 'ITEM_0002', 4000 FROM dual UNION ALL SELECT 'L1', 'ITEM_0003', 5000 FROM dual UNION ALL SELECT 'L2', 'ITEM_0010', 3000 FROM dual UNION ALL SELECT 'L2', 'ITEM_0011', 4000 FROM dual UNION ALL SELECT 'L2', 'ITEM_0012', 5000 FROM dual ) , co_cald_mat AS ( SELECT '20170717' dt, '201729' yw FROM dual UNION ALL SELECT '20170718', '201729' FROM dual UNION ALL SELECT '20170719', '201729' FROM dual UNION ALL SELECT '20170720', '201729' FROM dual UNION ALL SELECT '20170721', '201729' FROM dual UNION ALL SELECT '20170722', '201729' FROM dual UNION ALL SELECT '20170723', '201729' FROM dual UNION ALL SELECT '20170724', '201730' FROM dual UNION ALL SELECT '20170725', '201730' FROM dual UNION ALL SELECT '20170726', '201730' FROM dual UNION ALL SELECT '20170727', '201730' FROM dual UNION ALL SELECT '20170728', '201730' FROM dual UNION ALL SELECT '20170729', '201730' FROM dual UNION ALL SELECT '20170730', '201730' FROM dual UNION ALL SELECT '20170731', '201731A' FROM dual UNION ALL SELECT '20170801', '201731B' FROM dual UNION ALL SELECT '20170802', '201731B' FROM dual UNION ALL SELECT '20170803', '201731B' FROM dual UNION ALL SELECT '20170804', '201731B' FROM dual UNION ALL SELECT '20170805', '201731B' FROM dual UNION ALL SELECT '20170806', '201731B' FROM dual UNION ALL SELECT '20170807', '201732' FROM dual UNION ALL SELECT '20170808', '201732' FROM dual UNION ALL SELECT '20170809', '201732' FROM dual UNION ALL SELECT '20170810', '201732' FROM dual UNION ALL SELECT '20170811', '201732' FROM dual UNION ALL SELECT '20170812', '201732' FROM dual UNION ALL SELECT '20170813', '201732' FROM dual ) SELECT line_cd , item_cd , SUM(DECODE(yw, '201729' , qty)) "201729" , SUM(DECODE(yw, '201730' , qty)) "201730" , SUM(DECODE(yw, '201731A', qty)) "201731A" , SUM(DECODE(yw, '201731B', qty)) "201731B" , SUM(DECODE(yw, '201732' , qty)) "201732" , SUM(DECODE(yw, '201733' , qty)) "201733" FROM (SELECT a.line_cd , a.item_cd , b.dt , b.yw , LEAST(a.capa_qty, a.so_qty - (b.lv - a.x) * a.capa_qty) qty FROM (SELECT b.line_cd , a.priority , a.item_cd , a.so_qty , b.capa_qty , SUM(CEIL(a.so_qty / b.capa_qty)) OVER(PARTITION BY b.line_cd ORDER BY a.priority) - CEIL(a.so_qty / b.capa_qty) + 1 x , SUM(CEIL(a.so_qty / b.capa_qty)) OVER(PARTITION BY b.line_cd ORDER BY a.priority) y FROM co_so a , co_line_capa b WHERE a.item_cd = b.item_cd ) a , (SELECT a.line_cd, b.dt, b.yw , COUNT(*) OVER(PARTITION BY a.line_cd ORDER BY b.dt) lv FROM (SELECT DISTINCT line_cd FROM co_line_capa) a CROSS JOIN co_cald_mat b LEFT OUTER JOIN co_line_hol c ON b.dt >= REPLACE(SUBSTR(c.start_dt_time, 1, 10), '-') AND b.dt < REPLACE(SUBSTR(c.end_dt_time , 1, 10), '-') AND a.line_cd = DECODE(c.line_cd, 'ALL', a.line_cd, c.line_cd) WHERE b.dt >= '20170717' AND c.line_cd IS NULL ) b WHERE a.line_cd = b.line_cd AND b.lv BETWEEN a.x AND a.y ) GROUP BY line_cd, item_cd ORDER BY line_cd, item_cd ;