꿈꾸는 개발자, DBA 커뮤니티 구루비

납품일자별 MIN 값 데이터 가져오기 (FOR LOOP x) - 이전 질문 조금다른내용

by 홍길덩이 [PL/SQL] 오라클 [2019.04.16 11:17:46]

안녕하세요~

저번질문에서 마농님께서 답변을 올려 주셨었는데..

데이터가 이상한것 같다고 하셔서 다시 찾아보니 업체코드와 작업주 등 여러가지 요소가 빠져있었습니다.

데이터 추가하여 문의 드립니다.

W_NAME 과 W_TIME을 JOIN하여 정렬순서를 정하게 되는데

정렬 순서는 S_TIME 빠른순 > 아이템그룹 > 납품일 빠른순 > 아이템타입( 0 우선적용, 1,2,3,4....같은순위) > 용량 큰순

으로 정렬 되어야하며, 동일 정렬순서의 다른 라인일 경우 동일한 순번을 같습니다.

예를 들어 A (1) -> A (2) -> A (3) -> B (3) -> A (4) -> A(5) -> B(5) -> A(6) ...

이런 식으로 순번이 정해집니다.

그리고 작업주의 마지막 아이템과 그 다음 작업주의 아이템중 가장 빠른 정렬순서를 같는 아이템이 먼저 순서를 부여받게 됩니다.

잘 부탁드립니다(--)(__)

업체코드	아이템	아이템타입	작업주	아이템그룹	용량	납품일	작업구역
AP001-1	AT_20	1	1	AT	10	2019-04-01 0:00	A
AP001-2	AT_20	2	1	AT	20	2019-04-01 0:00	A
AP001-0	AT_20	0	2	AT	5	2019-04-02 0:00	A
AP003-1	AT_21	1	2	AT	18	2019-04-03 0:00	A
AP004-1	AT_22	1	2	AT	54	2019-04-02 0:00	C
AP005-1	AT_23	1	2	AT	8	2019-04-02 0:00	C
AP006-1	BA_20	1	2	BA	21	2019-04-02 0:00	A
AP007-1	BA_22	1	2	BA	41	2019-04-02 0:00	A
AP007-2	AT_22	2	2	AT	13	2019-04-04 0:00	A
AP007-3	AT_24	3	3	AT	1	2019-04-04 0:00	A
AP007-4	AT_25	4	3	AT	2	2019-04-04 0:00	A
AP001-1	AT_25	1	3	AT	3	2019-04-04 0:00	A
AP001-2	AT_26	2	3	AT	4	2019-04-04 0:00	A
AP002-1	AT_26	1	4	AT	7	2019-04-05 0:00	A
AP003-1	BA_27	1	4	BA	22	2019-04-05 0:00	A
AP004-1	AT_27	1	5	AT	26	2019-04-06 0:00	A
AP005-0	AT_28	0	5	AT	1	2019-04-07 0:00	A

--결과

업체코드	아이템	아이템타입	작업주	아이템그룹	용량	납품일	작업구역	순번
AP001-2	AT_20	2	1	AT	20	2019-04-01 0:00	A	1
AP001-1	AT_20	1	1	AT	10	2019-04-01 0:00	A	2
AP001-0	AT_20	0	2	AT	5	2019-04-02 0:00	A	3
AP004-1	AT_22	1	2	AT	54	2019-04-02 0:00	C	3
AP003-1	AT_21	1	2	AT	18	2019-04-03 0:00	A	4
AP005-1	AT_23	1	2	AT	8	2019-04-02 0:00	C	4
AP007-2	AT_22	2	2	AT	13	2019-04-04 0:00	A	5
AP007-1	BA_22	1	2	BA	41	2019-04-02 0:00	A	6
AP006-1	BA_20	1	2	BA	21	2019-04-02 0:00	A	7
AP001-2	AT_26	2	3	AT	4	2019-04-04 0:00	A	8
AP001-1	AT_25	1	3	AT	3	2019-04-04 0:00	A	9
AP007-3	AT_24	3	3	AT	1	2019-04-04 0:00	A	10
AP007-4	AT_25	4	3	AT	2	2019-04-04 0:00	A	11
AP002-1	AT_26	1	4	AT	7	2019-04-05 0:00	A	12
AP003-1	BA_27	1	4	BA	22	2019-04-05 0:00	A	13
AP004-1	AT_27	1	5	AT	26	2019-04-06 0:00	A	14
AP005-0	AT_28	0	5	AT	1	2019-04-07 0:00	A	15

--W_TIME

FROM_ITEM	TO_ITME	S_TIME
AT_20	AT_20	0
AT_20	AT_21	5
AT_20	AT_22	5
AT_20	AT_23	5
AT_20	AT_24	5
AT_20	AT_25	5
AT_20	AT_26	5
AT_20	AT_27	5
AT_20	AT_28	5
AT_20	BA_20	10
AT_20	BA_22	10
AT_20	BA_25	10
AT_20	BA_26	10
AT_20	BA_27	10
AT_21	AT_20	5
AT_21	AT_21	0
AT_21	AT_22	5
AT_21	AT_23	5
AT_21	AT_24	5
AT_21	AT_25	5
AT_21	AT_26	5
AT_21	AT_27	5
AT_21	AT_28	5
AT_21	BA_20	10
AT_21	BA_22	10
AT_21	BA_25	10
AT_21	BA_26	10
AT_21	BA_27	10
AT_22	AT_20	5
AT_22	AT_21	5
AT_22	AT_22	0
AT_22	AT_23	5
AT_22	AT_24	5
AT_22	AT_25	5
AT_22	AT_26	5
AT_22	AT_27	5
AT_22	AT_28	5
AT_22	BA_20	10
AT_22	BA_22	10
AT_22	BA_25	10
AT_22	BA_26	10
AT_22	BA_27	10
AT_23	AT_20	5
AT_23	AT_21	5
AT_23	AT_22	5
AT_23	AT_23	0
AT_23	AT_24	5
AT_23	AT_25	5
AT_23	AT_26	5
AT_23	AT_27	5
AT_23	AT_28	5
AT_23	BA_20	10
AT_23	BA_22	10
AT_23	BA_25	10
AT_23	BA_26	10
AT_23	BA_27	10
AT_24	AT_20	5
AT_24	AT_21	5
AT_24	AT_22	5
AT_24	AT_23	5
AT_24	AT_24	0
AT_24	AT_25	5
AT_24	AT_26	5
AT_24	AT_27	5
AT_24	AT_28	5
AT_24	BA_20	10
AT_24	BA_22	10
AT_24	BA_25	10
AT_24	BA_26	10
AT_24	BA_27	10
AT_25	AT_20	5
AT_25	AT_21	5
AT_25	AT_22	5
AT_25	AT_23	5
AT_25	AT_24	5
AT_25	AT_25	0
AT_25	AT_26	5
AT_25	AT_27	5
AT_25	AT_28	5
AT_25	BA_20	10
AT_25	BA_22	10
AT_25	BA_25	10
AT_25	BA_26	10
AT_25	BA_27	10
AT_26	AT_20	5
AT_26	AT_21	5
AT_26	AT_22	5
AT_26	AT_23	5
AT_26	AT_24	5
AT_26	AT_25	5
AT_26	AT_26	0
AT_26	AT_27	5
AT_26	AT_28	5
AT_26	BA_20	10
AT_26	BA_22	10
AT_26	BA_25	10
AT_26	BA_26	10
AT_26	BA_27	10
AT_27	AT_20	5
AT_27	AT_21	5
AT_27	AT_22	5
AT_27	AT_23	5
AT_27	AT_24	5
AT_27	AT_25	5
AT_27	AT_26	5
AT_27	AT_27	0
AT_27	AT_28	5
AT_27	BA_20	10
AT_27	BA_22	10
AT_27	BA_25	10
AT_27	BA_26	10
AT_27	BA_27	10
AT_28	AT_20	5
AT_28	AT_21	5
AT_28	AT_22	5
AT_28	AT_23	5
AT_28	AT_24	5
AT_28	AT_25	5
AT_28	AT_26	5
AT_28	AT_27	5
AT_28	AT_28	0
AT_28	BA_20	10
AT_28	BA_22	10
AT_28	BA_25	10
AT_28	BA_26	10
AT_28	BA_27	10
BA_20	AT_20	10
BA_20	AT_21	10
BA_20	AT_22	10
BA_20	AT_23	10
BA_20	AT_24	10
BA_20	AT_25	10
BA_20	AT_26	10
BA_20	AT_27	10
BA_20	AT_28	10
BA_20	BA_20	0
BA_20	BA_22	5
BA_20	BA_25	5
BA_20	BA_26	5
BA_20	BA_27	5
BA_22	AT_20	10
BA_22	AT_21	10
BA_22	AT_22	10
BA_22	AT_23	10
BA_22	AT_24	10
BA_22	AT_25	10
BA_22	AT_26	10
BA_22	AT_27	10
BA_22	AT_28	10
BA_22	BA_20	5
BA_22	BA_22	0
BA_22	BA_25	5
BA_22	BA_26	5
BA_22	BA_27	5
BA_25	AT_20	10
BA_25	AT_21	10
BA_25	AT_22	10
BA_25	AT_23	10
BA_25	AT_24	10
BA_25	AT_25	10
BA_25	AT_26	10
BA_25	AT_27	10
BA_25	AT_28	10
BA_25	BA_20	5
BA_25	BA_22	5
BA_25	BA_25	0
BA_25	BA_26	5
BA_25	BA_27	5
BA_26	AT_20	10
BA_26	AT_21	10
BA_26	AT_22	10
BA_26	AT_23	10
BA_26	AT_24	10
BA_26	AT_25	10
BA_26	AT_26	10
BA_26	AT_27	10
BA_26	AT_28	10
BA_26	BA_20	5
BA_26	BA_22	5
BA_26	BA_25	5
BA_26	BA_26	0
BA_26	BA_27	5
BA_27	AT_20	10
BA_27	AT_21	10
BA_27	AT_22	10
BA_27	AT_23	10
BA_27	AT_24	10
BA_27	AT_25	10
BA_27	AT_26	10
BA_27	AT_27	10
BA_27	AT_28	10
BA_27	BA_20	5
BA_27	BA_22	5
BA_27	BA_25	5
BA_27	BA_26	5
BA_27	BA_27	0

WITH W_NAME AS 
    (
    SELECT 'AP001-1' AS "업체코드", 'AT_20' AS "아이템", '1' AS "아이템타입", '1' AS "작업주", 'AT' AS "아이템그룹", 10 "용량", TO_DATE('20190401','YYYYMMDD') AS "납품일", 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-2', 'AT_20' AS "아이템", '2' AS "아이템타입", '1' AS "작업주", 'AT' AS "아이템그룹", 20 "용량", TO_DATE('20190401','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-0', 'AT_20' AS "아이템", '0' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 5 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP003-1', 'AT_21' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 18 "용량", TO_DATE('20190403','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP004-1', 'AT_22' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 54 "용량", TO_DATE('20190402','YYYYMMDD'), 'C' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP005-1', 'AT_23' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 8 "용량", TO_DATE('20190402','YYYYMMDD'), 'C' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP006-1', 'BA_20' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'BA' AS "아이템그룹", 21 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-1', 'BA_22' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'BA' AS "아이템그룹", 41 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-2', 'AT_22' AS "아이템", '2' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 13 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-3', 'AT_24' AS "아이템", '3' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 1 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-4', 'AT_25' AS "아이템", '4' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 2 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-1', 'AT_25' AS "아이템", '1' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 3 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-2', 'AT_26' AS "아이템", '2' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 4 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP002-1', 'AT_26' AS "아이템", '1' AS "아이템타입", '4' AS "작업주", 'AT' AS "아이템그룹", 7 "용량", TO_DATE('20190405','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP003-1', 'BA_27' AS "아이템", '1' AS "아이템타입", '4' AS "작업주", 'BA' AS "아이템그룹", 22 "용량", TO_DATE('20190405','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP004-1', 'AT_27' AS "아이템", '1' AS "아이템타입", '5' AS "작업주", 'AT' AS "아이템그룹", 26 "용량", TO_DATE('20190406','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP005-0', 'AT_28' AS "아이템", '0' AS "아이템타입", '5' AS "작업주", 'AT' AS "아이템그룹", 1 "용량", TO_DATE('20190407','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    )
,	W_TIME AS
    (
    SELECT 'AT_20' FROM_ITEM,	'AT_20' TO_ITEM, 0 AS S_TIME  FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_21' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_22' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_23' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_24' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_25' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_26' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_27' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_28' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_20' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_22' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_25' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_26' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_27' TO_ITEM, 0    FROM DUAL
    )

by 마농 [2019.04.16 13:20:49]

두번째 표가 원하는 결과인가요?
이 결과에 대한 설명을 해주세요.
왜 이런 결과가 나오게 되는지 과정을 차근 차근 설명해 주세요.

by 홍길덩이 [2019.04.16 13:40:24]

작업주와 정렬순서대로 작성하여 나온 결과입니다.

어떤 부분에서 이상한지 알려주시면 말씀드리겠습니다.

-- S_TIME 적용 후 (순번추가)

업체코드	아이템	아이템타입	작업주	아이템그룹	용량	납품일	작업구역	순번
AP001-2	AT_20	2	1	AT	20	2019-04-01 0:00	A	1
AP001-1	AT_20	1	1	AT	10	2019-04-01 0:00	A	2
AP001-0	AT_20	0	2	AT	5	2019-04-02 0:00	A	3
AP004-1	AT_22	1	2	AT	54	2019-04-02 0:00	C	3
AP005-1	AT_23	1	2	AT	8	2019-04-02 0:00	C	4
AP003-1	AT_21	1	2	AT	18	2019-04-03 0:00	A	4
AP007-2	AT_22	2	2	AT	13	2019-04-04 0:00	A	5
AP007-1	BA_22	1	2	BA	41	2019-04-02 0:00	A	6
AP006-1	BA_20	1	2	BA	21	2019-04-02 0:00	A	7
AP001-2	AT_26	2	3	AT	4	2019-04-04 0:00	A	8
AP001-1	AT_25	1	3	AT	3	2019-04-04 0:00	A	9
AP007-3	AT_24	3	3	AT	1	2019-04-04 0:00	A	10
AP007-4	AT_25	4	3	AT	2	2019-04-04 0:00	A	11
AP002-1	AT_26	1	4	AT	7	2019-04-05 0:00	A	12
AP003-1	BA_27	1	4	BA	22	2019-04-05 0:00	A	13
AP004-1	AT_27	1	5	AT	26	2019-04-06 0:00	A	14
AP005-0	AT_28	0	5	AT	1	2019-04-07 0:00	A	14

맨마지막 작업구역에 B로 되어있는데 A로 보시면 됩니다.

by 마농 [2019.04.16 13:43:08]

한줄 한줄 따라가면서 자세히 설명해 주세요.

by 홍길덩이 [2019.04.16 14:11:28]

아이템 생산하는 과정에서 가장 최적의 결과를 얻기위해

S_TIME 빠른순 > 아이템그룹 > 납품일 빠른순 > 아이템타입( 0 우선적용, 1,2,3,4....같은순위) > 용량 큰순

정렬 순서대로 생산을 하게 됩니다.

작업주는 1주차 2주차 3주차... 순

작업구역 A , B, C, D.. 순

순번1번의 경우

- 업체코드 AP001-2 , 아이템 AT_20, 용량 20, 납품일 2019-04-01 0:00 되며,

순번2번의 경우

같은 작업주이면서 순번1번과 연결되는 아이템이 생산되어야 합니다.

업체코드 AP001-1 아이템 AT_20, 옹량 10, 납품일 2019-04-01 0:00, AT_20 ->AT_20 연결의 경우 S_TIME = 0 이므로 정렬순서의 따라 순번2번이 됩니다.

순번3번의 A 작업구역의 경우

이전 1 작업주의 마지막 아이템과 연결하여 생산하기 위해 2 작업주의 아이템중 정렬순서의 따라

업체코드 AP001-0, 아이템 AT_20, 아이템타입 0, 용량 5, 납품일 2019-04-02 0:00 로 다른 아이템의 비해 용량은 작지만

AT_20 -> AT_20의 S_TIME 은 0 이면서 우선적용인 아이템 타입이 0이며 납품일자가 가장 빠르기 때문에 순번 3번이 됩니다.

순번 3번에는 A,C 작업 구역이 같이 있는데 같은 주자에 여러개의 작업구역이 있는경우 같은 순번을 가지게 됩니다.

C 작업구역의 순번 4번은 순번3번의 C 작업구역과 S_TIME 이 5 이면서 납품일이 가장 빠르기 때문에 순번 4번을 가지게 됩니다.

by 홍길덩이 [2019.04.16 14:11:55]

더 셜명이 필요한가요? ^^;;

by 홍길덩이 [2019.04.16 14:21:56]

순번4번의 A작업 구역은

순번3번의 A작업 구역의 아이템과

S_TIME 빠른순 > 아이템그룹 > 납품일 빠른순 > 아이템타입( 0 우선적용, 1,2,3,4....같은순위) > 용량 큰순

으로 데이터를 보면 2주차의 AT_20 과 S_TIME가장 적은 아이템은 AT_21, AT_22 이며

그 중 납품일이 가장 빠르기 때문에

업체코드 AP003-1, 아이템 AT_21, 18 용량 2019-04-03 0:00 이 됩니다.

순번 5번의 A작업구역은

순번 4번의 A자업구역의 아이템과 S_TIME이 가장 적은 업체코드 AP_007-2, 아이템 AT_22, 13 용량, 2019-04-04 0:00 납품일 이 됩니다.

by 마농 [2019.04.16 14:54:49]

자료가 왔다 갔다 서로 불일치 하네요.
원본표, 결과표, WITH 문, 댓글표 서로 불일치.
다 지우고 정확하게 일치하는 자료로 새로 올려주세요.

by 홍길덩이 [2019.04.16 15:10:59]

죄송합니다. 다시올립니다.

--원본

업체코드	아이템	아이템타입	작업주	아이템그룹	용량	납품일	작업구역
AP001-1	AT_20	1	1	AT	10	2019-04-01 0:00	A
AP001-2	AT_20	2	1	AT	20	2019-04-01 0:00	A
AP001-0	AT_20	0	2	AT	5	2019-04-02 0:00	A
AP003-1	AT_21	1	2	AT	18	2019-04-03 0:00	A
AP004-1	AT_22	1	2	AT	54	2019-04-02 0:00	C
AP005-1	AT_23	1	2	AT	8	2019-04-02 0:00	C
AP006-1	BA_20	1	2	BA	21	2019-04-02 0:00	A
AP007-1	BA_22	1	2	BA	41	2019-04-02 0:00	A
AP007-2	AT_22	2	2	AT	13	2019-04-04 0:00	A
AP007-3	AT_24	3	3	AT	1	2019-04-04 0:00	A
AP007-4	AT_25	4	3	AT	2	2019-04-04 0:00	A
AP001-1	AT_25	1	3	AT	3	2019-04-04 0:00	A
AP001-2	AT_26	2	3	AT	4	2019-04-04 0:00	A
AP002-1	AT_26	1	4	AT	7	2019-04-05 0:00	A
AP003-1	BA_27	1	4	BA	22	2019-04-05 0:00	A
AP004-1	AT_27	1	5	AT	26	2019-04-06 0:00	A
AP005-0	AT_28	0	5	AT	1	2019-04-07 0:00	A

--결과

업체코드	아이템	아이템타입	작업주	아이템그룹	용량	납품일	작업구역	순번
AP001-2	AT_20	2	1	AT	20	2019-04-01 0:00	A	1
AP001-1	AT_20	1	1	AT	10	2019-04-01 0:00	A	2
AP001-0	AT_20	0	2	AT	5	2019-04-02 0:00	A	3
AP004-1	AT_22	1	2	AT	54	2019-04-02 0:00	C	3
AP003-1	AT_21	1	2	AT	18	2019-04-03 0:00	A	4
AP005-1	AT_23	1	2	AT	8	2019-04-02 0:00	C	4
AP007-2	AT_22	2	2	AT	13	2019-04-04 0:00	A	5
AP007-1	BA_22	1	2	BA	41	2019-04-02 0:00	A	6
AP006-1	BA_20	1	2	BA	21	2019-04-02 0:00	A	7
AP001-2	AT_26	2	3	AT	4	2019-04-04 0:00	A	8
AP001-1	AT_25	1	3	AT	3	2019-04-04 0:00	A	9
AP007-3	AT_24	3	3	AT	1	2019-04-04 0:00	A	10
AP007-4	AT_25	4	3	AT	2	2019-04-04 0:00	A	11
AP002-1	AT_26	1	4	AT	7	2019-04-05 0:00	A	12
AP003-1	BA_27	1	4	BA	22	2019-04-05 0:00	A	13
AP004-1	AT_27	1	5	AT	26	2019-04-06 0:00	A	14
AP005-0	AT_28	0	5	AT	1	2019-04-07 0:00	A	15

--W_TIME

FROM_ITEM	TO_ITME	S_TIME
AT_20	AT_20	0
AT_20	AT_21	5
AT_20	AT_22	5
AT_20	AT_23	5
AT_20	AT_24	5
AT_20	AT_25	5
AT_20	AT_26	5
AT_20	AT_27	5
AT_20	AT_28	5
AT_20	BA_20	10
AT_20	BA_22	10
AT_20	BA_25	10
AT_20	BA_26	10
AT_20	BA_27	10
AT_21	AT_20	5
AT_21	AT_21	0
AT_21	AT_22	5
AT_21	AT_23	5
AT_21	AT_24	5
AT_21	AT_25	5
AT_21	AT_26	5
AT_21	AT_27	5
AT_21	AT_28	5
AT_21	BA_20	10
AT_21	BA_22	10
AT_21	BA_25	10
AT_21	BA_26	10
AT_21	BA_27	10
AT_22	AT_20	5
AT_22	AT_21	5
AT_22	AT_22	0
AT_22	AT_23	5
AT_22	AT_24	5
AT_22	AT_25	5
AT_22	AT_26	5
AT_22	AT_27	5
AT_22	AT_28	5
AT_22	BA_20	10
AT_22	BA_22	10
AT_22	BA_25	10
AT_22	BA_26	10
AT_22	BA_27	10
AT_23	AT_20	5
AT_23	AT_21	5
AT_23	AT_22	5
AT_23	AT_23	0
AT_23	AT_24	5
AT_23	AT_25	5
AT_23	AT_26	5
AT_23	AT_27	5
AT_23	AT_28	5
AT_23	BA_20	10
AT_23	BA_22	10
AT_23	BA_25	10
AT_23	BA_26	10
AT_23	BA_27	10
AT_24	AT_20	5
AT_24	AT_21	5
AT_24	AT_22	5
AT_24	AT_23	5
AT_24	AT_24	0
AT_24	AT_25	5
AT_24	AT_26	5
AT_24	AT_27	5
AT_24	AT_28	5
AT_24	BA_20	10
AT_24	BA_22	10
AT_24	BA_25	10
AT_24	BA_26	10
AT_24	BA_27	10
AT_25	AT_20	5
AT_25	AT_21	5
AT_25	AT_22	5
AT_25	AT_23	5
AT_25	AT_24	5
AT_25	AT_25	0
AT_25	AT_26	5
AT_25	AT_27	5
AT_25	AT_28	5
AT_25	BA_20	10
AT_25	BA_22	10
AT_25	BA_25	10
AT_25	BA_26	10
AT_25	BA_27	10
AT_26	AT_20	5
AT_26	AT_21	5
AT_26	AT_22	5
AT_26	AT_23	5
AT_26	AT_24	5
AT_26	AT_25	5
AT_26	AT_26	0
AT_26	AT_27	5
AT_26	AT_28	5
AT_26	BA_20	10
AT_26	BA_22	10
AT_26	BA_25	10
AT_26	BA_26	10
AT_26	BA_27	10
AT_27	AT_20	5
AT_27	AT_21	5
AT_27	AT_22	5
AT_27	AT_23	5
AT_27	AT_24	5
AT_27	AT_25	5
AT_27	AT_26	5
AT_27	AT_27	0
AT_27	AT_28	5
AT_27	BA_20	10
AT_27	BA_22	10
AT_27	BA_25	10
AT_27	BA_26	10
AT_27	BA_27	10
AT_28	AT_20	5
AT_28	AT_21	5
AT_28	AT_22	5
AT_28	AT_23	5
AT_28	AT_24	5
AT_28	AT_25	5
AT_28	AT_26	5
AT_28	AT_27	5
AT_28	AT_28	0
AT_28	BA_20	10
AT_28	BA_22	10
AT_28	BA_25	10
AT_28	BA_26	10
AT_28	BA_27	10
BA_20	AT_20	10
BA_20	AT_21	10
BA_20	AT_22	10
BA_20	AT_23	10
BA_20	AT_24	10
BA_20	AT_25	10
BA_20	AT_26	10
BA_20	AT_27	10
BA_20	AT_28	10
BA_20	BA_20	0
BA_20	BA_22	5
BA_20	BA_25	5
BA_20	BA_26	5
BA_20	BA_27	5
BA_22	AT_20	10
BA_22	AT_21	10
BA_22	AT_22	10
BA_22	AT_23	10
BA_22	AT_24	10
BA_22	AT_25	10
BA_22	AT_26	10
BA_22	AT_27	10
BA_22	AT_28	10
BA_22	BA_20	5
BA_22	BA_22	0
BA_22	BA_25	5
BA_22	BA_26	5
BA_22	BA_27	5
BA_25	AT_20	10
BA_25	AT_21	10
BA_25	AT_22	10
BA_25	AT_23	10
BA_25	AT_24	10
BA_25	AT_25	10
BA_25	AT_26	10
BA_25	AT_27	10
BA_25	AT_28	10
BA_25	BA_20	5
BA_25	BA_22	5
BA_25	BA_25	0
BA_25	BA_26	5
BA_25	BA_27	5
BA_26	AT_20	10
BA_26	AT_21	10
BA_26	AT_22	10
BA_26	AT_23	10
BA_26	AT_24	10
BA_26	AT_25	10
BA_26	AT_26	10
BA_26	AT_27	10
BA_26	AT_28	10
BA_26	BA_20	5
BA_26	BA_22	5
BA_26	BA_25	5
BA_26	BA_26	0
BA_26	BA_27	5
BA_27	AT_20	10
BA_27	AT_21	10
BA_27	AT_22	10
BA_27	AT_23	10
BA_27	AT_24	10
BA_27	AT_25	10
BA_27	AT_26	10
BA_27	AT_27	10
BA_27	AT_28	10
BA_27	BA_20	5
BA_27	BA_22	5
BA_27	BA_25	5
BA_27	BA_26	5
BA_27	BA_27	0

WITH W_NAME AS 
    (
    SELECT 'AP001-1' AS "업체코드", 'AT_20' AS "아이템", '1' AS "아이템타입", '1' AS "작업주", 'AT' AS "아이템그룹", 10 "용량", TO_DATE('20190401','YYYYMMDD') AS "납품일", 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-2', 'AT_20' AS "아이템", '2' AS "아이템타입", '1' AS "작업주", 'AT' AS "아이템그룹", 20 "용량", TO_DATE('20190401','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-0', 'AT_20' AS "아이템", '0' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 5 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP003-1', 'AT_21' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 18 "용량", TO_DATE('20190403','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP004-1', 'AT_22' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 54 "용량", TO_DATE('20190402','YYYYMMDD'), 'C' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP005-1', 'AT_23' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 8 "용량", TO_DATE('20190402','YYYYMMDD'), 'C' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP006-1', 'BA_20' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'BA' AS "아이템그룹", 21 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-1', 'BA_22' AS "아이템", '1' AS "아이템타입", '2' AS "작업주", 'BA' AS "아이템그룹", 41 "용량", TO_DATE('20190402','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-2', 'AT_22' AS "아이템", '2' AS "아이템타입", '2' AS "작업주", 'AT' AS "아이템그룹", 13 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-3', 'AT_24' AS "아이템", '3' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 1 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP007-4', 'AT_25' AS "아이템", '4' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 2 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-1', 'AT_25' AS "아이템", '1' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 3 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP001-2', 'AT_26' AS "아이템", '2' AS "아이템타입", '3' AS "작업주", 'AT' AS "아이템그룹", 4 "용량", TO_DATE('20190404','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP002-1', 'AT_26' AS "아이템", '1' AS "아이템타입", '4' AS "작업주", 'AT' AS "아이템그룹", 7 "용량", TO_DATE('20190405','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP003-1', 'BA_27' AS "아이템", '1' AS "아이템타입", '4' AS "작업주", 'BA' AS "아이템그룹", 22 "용량", TO_DATE('20190405','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP004-1', 'AT_27' AS "아이템", '1' AS "아이템타입", '5' AS "작업주", 'AT' AS "아이템그룹", 26 "용량", TO_DATE('20190406','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    UNION ALL SELECT 'AP005-0', 'AT_28' AS "아이템", '0' AS "아이템타입", '5' AS "작업주", 'AT' AS "아이템그룹", 1 "용량", TO_DATE('20190407','YYYYMMDD'), 'A' AS "작업구역" FROM DUAL
    )
,	W_TIME AS
    (
    SELECT 'AT_20' FROM_ITEM,	'AT_20' TO_ITEM, 0 AS S_TIME  FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_20' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_21' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_21' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_22' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_22' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_23' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_23' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_24' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_24' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_25' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_25' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_26' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_26' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_27' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'AT_28' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_27' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_21' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_23' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_24' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'AT_28' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'AT_28' FROM_ITEM,	'BA_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_20' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_20' FROM_ITEM,	'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_22' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_22' FROM_ITEM,	'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_25' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_25' FROM_ITEM,	'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_26' TO_ITEM, 0    FROM DUAL
    UNION ALL SELECT 'BA_26' FROM_ITEM,	'BA_27' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_20' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_21' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_22' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_23' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_24' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_25' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_26' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_27' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'AT_28' TO_ITEM, 10   FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_20' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_22' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_25' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_26' TO_ITEM, 5    FROM DUAL
    UNION ALL SELECT 'BA_27' FROM_ITEM,	'BA_27' TO_ITEM, 0    FROM DUAL
    )

by 홍길덩이 [2019.04.16 16:53:21]

질문에 있는 표와 WITH 문도 수정하여 다시 올렸습니다.

by 마농 [2019.04.17 16:22:04]

WITH w_name AS
(
SELECT 'AP001-1' 업체코드, 'AT_20' 아이템, '1' 아이템타입, '1' 작업주, 'AT' 아이템그룹, 10 용량, '20190401' 납품일, 'A' 작업구역 FROM dual
UNION ALL SELECT 'AP001-2', 'AT_20', '2', '1', 'AT', 20, '20190401', 'A' FROM dual
UNION ALL SELECT 'AP001-0', 'AT_20', '0', '2', 'AT',  5, '20190402', 'A' FROM dual
UNION ALL SELECT 'AP003-1', 'AT_21', '1', '2', 'AT', 18, '20190403', 'A' FROM dual
UNION ALL SELECT 'AP004-1', 'AT_22', '1', '2', 'AT', 54, '20190402', 'C' FROM dual
UNION ALL SELECT 'AP005-1', 'AT_23', '1', '2', 'AT',  8, '20190402', 'C' FROM dual
UNION ALL SELECT 'AP006-1', 'BA_20', '1', '2', 'BA', 21, '20190402', 'A' FROM dual
UNION ALL SELECT 'AP007-1', 'BA_22', '1', '2', 'BA', 41, '20190402', 'A' FROM dual
UNION ALL SELECT 'AP007-2', 'AT_22', '2', '2', 'AT', 13, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP007-3', 'AT_24', '3', '3', 'AT',  1, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP007-4', 'AT_25', '4', '3', 'AT',  2, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP001-1', 'AT_25', '1', '3', 'AT',  3, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP001-2', 'AT_26', '2', '3', 'AT',  4, '20190404', 'A' FROM dual
UNION ALL SELECT 'AP002-1', 'AT_26', '1', '4', 'AT',  7, '20190405', 'A' FROM dual
UNION ALL SELECT 'AP003-1', 'BA_27', '1', '4', 'BA', 22, '20190405', 'A' FROM dual
UNION ALL SELECT 'AP004-1', 'AT_27', '1', '5', 'AT', 26, '20190406', 'A' FROM dual
UNION ALL SELECT 'AP005-0', 'AT_28', '0', '5', 'AT',  1, '20190407', 'A' FROM dual
)
, w_time AS
(
SELECT 'AT_20' from_item, 'AT_20' to_item, 0 s_time FROM dual
UNION ALL SELECT 'AT_20', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_20', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_20', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_20', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_20', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_20', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_20', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_21', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_21',  0 FROM dual
UNION ALL SELECT 'AT_21', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_21', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_21', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_21', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_21', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_21', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_21', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_22', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_22',  0 FROM dual
UNION ALL SELECT 'AT_22', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_22', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_22', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_22', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_22', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_22', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_22', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_23', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_23',  0 FROM dual
UNION ALL SELECT 'AT_23', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_23', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_23', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_23', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_23', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_23', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_23', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_24', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_24',  0 FROM dual
UNION ALL SELECT 'AT_24', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_24', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_24', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_24', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_24', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_24', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_24', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_25', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_25',  0 FROM dual
UNION ALL SELECT 'AT_25', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_25', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_25', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_25', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_25', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_25', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_25', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_26', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_26',  0 FROM dual
UNION ALL SELECT 'AT_26', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_26', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_26', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_26', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_26', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_26', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_26', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_27', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_27', 'AT_27',  0 FROM dual
UNION ALL SELECT 'AT_27', 'AT_28',  5 FROM dual
UNION ALL SELECT 'AT_27', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_27', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_27', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_27', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_27', 'BA_27', 10 FROM dual
UNION ALL SELECT 'AT_28', 'AT_20',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_21',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_22',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_23',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_24',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_25',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_26',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_27',  5 FROM dual
UNION ALL SELECT 'AT_28', 'AT_28',  0 FROM dual
UNION ALL SELECT 'AT_28', 'BA_20', 10 FROM dual
UNION ALL SELECT 'AT_28', 'BA_22', 10 FROM dual
UNION ALL SELECT 'AT_28', 'BA_25', 10 FROM dual
UNION ALL SELECT 'AT_28', 'BA_26', 10 FROM dual
UNION ALL SELECT 'AT_28', 'BA_27', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_20', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_20', 'BA_20',  0 FROM dual
UNION ALL SELECT 'BA_20', 'BA_22',  5 FROM dual
UNION ALL SELECT 'BA_20', 'BA_25',  5 FROM dual
UNION ALL SELECT 'BA_20', 'BA_26',  5 FROM dual
UNION ALL SELECT 'BA_20', 'BA_27',  5 FROM dual
UNION ALL SELECT 'BA_22', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_22', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_22', 'BA_20',  5 FROM dual
UNION ALL SELECT 'BA_22', 'BA_22',  0 FROM dual
UNION ALL SELECT 'BA_22', 'BA_25',  5 FROM dual
UNION ALL SELECT 'BA_22', 'BA_26',  5 FROM dual
UNION ALL SELECT 'BA_22', 'BA_27',  5 FROM dual
UNION ALL SELECT 'BA_25', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_25', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_25', 'BA_20',  5 FROM dual
UNION ALL SELECT 'BA_25', 'BA_22',  5 FROM dual
UNION ALL SELECT 'BA_25', 'BA_25',  0 FROM dual
UNION ALL SELECT 'BA_25', 'BA_26',  5 FROM dual
UNION ALL SELECT 'BA_25', 'BA_27',  5 FROM dual
UNION ALL SELECT 'BA_26', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_26', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_26', 'BA_20',  5 FROM dual
UNION ALL SELECT 'BA_26', 'BA_22',  5 FROM dual
UNION ALL SELECT 'BA_26', 'BA_25',  5 FROM dual
UNION ALL SELECT 'BA_26', 'BA_26',  0 FROM dual
UNION ALL SELECT 'BA_26', 'BA_27',  5 FROM dual
UNION ALL SELECT 'BA_27', 'AT_20', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_21', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_22', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_23', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_24', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_25', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_26', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_27', 10 FROM dual
UNION ALL SELECT 'BA_27', 'AT_28', 10 FROM dual
UNION ALL SELECT 'BA_27', 'BA_20',  5 FROM dual
UNION ALL SELECT 'BA_27', 'BA_22',  5 FROM dual
UNION ALL SELECT 'BA_27', 'BA_25',  5 FROM dual
UNION ALL SELECT 'BA_27', 'BA_26',  5 FROM dual
UNION ALL SELECT 'BA_27', 'BA_27',  0 FROM dual
)
, w_name1 AS
(
SELECT 업체코드, 아이템, 아이템타입, 작업주, 아이템그룹, 용량, 납품일, 작업구역
     , ROW_NUMBER() OVER(PARTITION BY 작업구역
                             ORDER BY 작업주, 아이템그룹, 납품일, SIGN(아이템타입), 용량 DESC, 아이템) rn1
     , ROW_NUMBER() OVER(PARTITION BY 아이템
                             ORDER BY 작업주, 아이템그룹, 납품일, SIGN(아이템타입), 용량 DESC) rn
  FROM w_name
)
, w_time1 AS
(
SELECT a.from_item
     , b.rn from_rn
     , c.작업구역
     , a.to_item
     , c.rn to_rn
     , c.납품일
     , a.s_time
     , c.용량
  FROM w_time a
     , w_name1 b
     , w_name1 c
 WHERE a.from_item = b.아이템
   AND a.to_item   = c.아이템
)
, w_temp1(업체코드, 아이템, 아이템타입, 작업주, 아이템그룹, 용량, 납품일, 작업구역, rn, lv, pp, to_item_rn) AS
(
SELECT a.업체코드, a.아이템, a.아이템타입, a.작업주, a.아이템그룹, a.용량, a.납품일, a.작업구역
     , a.rn
     , 1 lv
     , CAST('>'|| a.아이템 ||'/'|| a.rn AS VARCHAR2(999)) pp
     , (SELECT MIN(c.아이템 ||'/'|| c.rn) KEEP(DENSE_RANK FIRST 
               ORDER BY c.작업주, b.s_time, c.아이템그룹, c.납품일, SIGN(c.아이템타입), c.용량 DESC, c.아이템)
          FROM w_time1 b
             , w_name1 c
         WHERE b.from_item = a.아이템
           AND b.from_rn   = a.rn
           AND b.to_item   = c.아이템
           AND b.to_rn     = c.rn
           AND c.작업구역  = a.작업구역
           AND c.rn1      != 1
        ) to_item_rn
  FROM w_name1 a
 WHERE rn1 = 1
 UNION ALL
SELECT a.업체코드, a.아이템, a.아이템타입, a.작업주, a.아이템그룹, a.용량, a.납품일, a.작업구역
     , a.rn
     , d.lv + 1 lv
     , d.pp ||'>'|| a.아이템 ||'/'|| a.rn pp
     , (SELECT MIN(c.아이템 ||'/'|| c.rn) KEEP(DENSE_RANK FIRST 
               ORDER BY c.작업주, b.s_time, c.아이템그룹, c.납품일, SIGN(c.아이템타입), c.용량 DESC, c.아이템)
          FROM w_time1 b
             , w_name1 c
         WHERE b.from_item = a.아이템
           AND b.from_rn   = a.rn
           AND b.to_item   = c.아이템
           AND b.to_rn     = c.rn
           AND c.작업구역  = a.작업구역
           AND INSTR(d.pp||'>'|| a.아이템 ||'/'|| a.rn, '>'||c.아이템||'/'||c.rn) = 0
        ) to_item_rn
  FROM w_name1 a
     , w_temp1 d
 WHERE a.작업구역 = d.작업구역
   AND a.아이템||'/'||a.rn = d.to_item_rn
   AND INSTR(d.pp||'>', '>'||a.아이템||'/'||a.rn||'>') = 0
)
SELECT 업체코드, 아이템, 아이템타입, 작업주, 아이템그룹, 용량, 납품일, 작업구역
     , DENSE_RANK() OVER(ORDER BY 작업주, rn) 순번
  FROM (SELECT 업체코드, 아이템, 아이템타입, 작업주, 아이템그룹, 용량, 납품일, 작업구역
             , lv
             , ROW_NUMBER() OVER(PARTITION BY 작업주, 작업구역 ORDER BY lv) rn
          FROM w_temp1
        )
 ORDER BY 작업주, rn, 작업구역
;

by 홍길덩이 [2019.04.18 16:10:14]

감사합니다~

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변