데이터베이스 질문과 답변
마농님 안녕하세요.
다시 한번 비슷한 개념의 질문을 드려야 되서 죄송할 따름입니다.
쿼리란 참 어렵네요.. ㅜㅜ
고민 많이 해보고 질문 올립니다.
월 별 캐파를 다르게 적용하는 부분과 LINE 및 NECK 공정까지 추가 되니 로직 짜기가 힘드네요..
이번 건은 거의 대부분 본인이 우선순위를 맞춰가야 하는데요.
CO_SO - Sales Order 의 정보는 RDD_DT 의 기준으로 빠른 날짜 순으로 우선순위를 높게 가져갑니다. (ORDER BY RDD_DT ASC)
Capa 적용
- 월별로 다른 Capa 적용
- CAPA_QTY 가 작은 것이 우선순위가 높다. (RANK() OVER(PARTITION BY LINE_CD, ITEM_CD, YYYYMM ORDER BY CAPA_QTY) AS PRIORITY)
- NECK_CD 는 라인에 따른 공정 개념으로 생각해주시면 됩니다.
- 일 별 Capa 가 남은 것은 Conversion 하여 다음 아이템에 적용한다. (Line, Neck, Item 에 따른 Capa)
결과는 CO_PROD_RST 와 같은 결과를 얻고 싶어요..
이미지 마지막꺼 입니다.
INSERT INTO CO_LINE_MST SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD FROM DUAL UNION ALL SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD FROM DUAL INSERT INTO CO_SO SELECT 'ITEM_0001' AS ITEM_CD, '20170710' AS RDD_DT, '20000' AS SO_QTY FROM DUAL UNION ALL SELECT 'ITEM_0002' AS ITEM_CD, '20170810' AS RDD_DT, '20000' AS SO_QTY FROM DUAL UNION ALL SELECT 'ITEM_0003' AS ITEM_CD, '20170820' AS RDD_DT, '25000' AS SO_QTY FROM DUAL UNION ALL SELECT 'ITEM_0004' AS ITEM_CD, '20170820' AS RDD_DT, '15000' AS SO_QTY FROM DUAL INSERT INTO CO_ITEM_PROD SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD, 'TABB' AS NECK_CD, '201707' AS YYYYMM, '3456' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD, 'LAMI' AS NECK_CD, '201707' AS YYYYMM, '3214.08' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD, 'TABB' AS NECK_CD, '201708' AS YYYYMM, '3456' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD, 'LAMI' AS NECK_CD, '201708' AS YYYYMM, '3110.4' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD, 'TABB' AS NECK_CD, '201707' AS YYYYMM, '3384' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD, 'LAMI' AS NECK_CD, '201707' AS YYYYMM, '3420' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD, 'TABB' AS NECK_CD, '201708' AS YYYYMM, '3283.2' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD, 'LAMI' AS NECK_CD, '201708' AS YYYYMM, '3456' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD, 'TABB' AS NECK_CD, '201707' AS YYYYMM, '3384' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD, 'LAMI' AS NECK_CD, '201707' AS YYYYMM, '3420' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD, 'TABB' AS NECK_CD, '201708' AS YYYYMM, '3283.2' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD, 'LAMI' AS NECK_CD, '201708' AS YYYYMM, '3456' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD, 'TABB' AS NECK_CD, '201707' AS YYYYMM, '3384' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD, 'LAMI' AS NECK_CD, '201707' AS YYYYMM, '3420' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD, 'TABB' AS NECK_CD, '201708' AS YYYYMM, '3283.2' AS CAPA_QTY FROM DUAL UNION ALL SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD, 'LAMI' AS NECK_CD, '201708' AS YYYYMM, '3456' AS CAPA_QTY FROM DUAL INSERT INTO CALD_MST SELECT '20170727' AS CALD_DT, '201730' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170728' AS CALD_DT, '201730' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170729' AS CALD_DT, '201730' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170730' AS CALD_DT, '201731A' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170731' AS CALD_DT, '201731A' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170801' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170802' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170803' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170804' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170805' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170806' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170807' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170808' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170809' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170810' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170811' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170812' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170813' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170814' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170815' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170816' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170817' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170818' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170819' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL SELECT '20170820' AS CALD_DT, '201734' AS YYYY_PART_WK_CD FROM DUAL
by 마농
[2017.06.26 11:32:26]
8월1일에 capa 가 바뀌어야 하는것 아닌가요?
by 잭키올
[2017.06.26 12:41:09]
아 죄송합니다.. 결과 이미지 교체 하였습니다. 다시 한번 확인 부탁드려요 ㅜㅜ
by 마농
[2017.06.26 13:06:11]
안바뀐 듯?
by 잭키올
[2017.06.26 13:09:56]
빨간색으로 체크한 부분이 바뀐 부분인데요. 뭐가 잘못 되었나요..?
by 마농
[2017.06.26 13:30:17]
월별로 capa 를 다르게 가져가야 하는 것 아닌가요?
아이템1 이 7월31일까지 3214.08 이었다면? 8월1일부터는 3110.40 으로 바뀌어야 하는 것 아닌가요?
by 잭키올
[2017.06.26 13:57:24]
맞습니다. 다시 올리겠습니다..
by 잭키올
[2017.06.26 14:02:57]
파일 수정하였습니다..
by 잭키올
[2017.06.26 17:08:22]
마농님.. 답변 가능하신가요?
by 마농
[2017.06.27 17:24:28]
capa 만 바꿔 놓았네요?
capa 가 바뀌면서 use 값 등, 관련 값들을 따라가면서 수정해 주셔야죠.
by 知音
[2017.06.26 14:03:32]
참고하세요 mssql-2005 버젼입니다.
WITH CO_LINE_MST AS(
SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD FROM DUAL UNION ALL
SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD FROM DUAL
), CO_SO AS (
SELECT 'ITEM_0001' AS ITEM_CD, '20170710' AS RDD_DT, 20000 AS SO_QTY FROM DUAL UNION ALL
SELECT 'ITEM_0002' AS ITEM_CD, '20170810' AS RDD_DT, 20000 AS SO_QTY FROM DUAL UNION ALL
SELECT 'ITEM_0003' AS ITEM_CD, '20170820' AS RDD_DT, 25000 AS SO_QTY FROM DUAL UNION ALL
SELECT 'ITEM_0004' AS ITEM_CD, '20170820' AS RDD_DT, 15000 AS SO_QTY FROM DUAL
), CO_ITEM_PROD AS (
SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD, 'TABB' AS NECK_CD, '201707' AS YYYYMM, 3456 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD, 'LAMI' AS NECK_CD, '201707' AS YYYYMM, 3214.08 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD, 'TABB' AS NECK_CD, '201708' AS YYYYMM, 3456 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L1' AS LINE_CD, 'ITEM_0001' AS ITEM_CD, 'LAMI' AS NECK_CD, '201708' AS YYYYMM, 3110.4 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD, 'TABB' AS NECK_CD, '201707' AS YYYYMM, 3384 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD, 'LAMI' AS NECK_CD, '201707' AS YYYYMM, 3420 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD, 'TABB' AS NECK_CD, '201708' AS YYYYMM, 3283.2 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L1' AS LINE_CD, 'ITEM_0003' AS ITEM_CD, 'LAMI' AS NECK_CD, '201708' AS YYYYMM, 3456 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD, 'TABB' AS NECK_CD, '201707' AS YYYYMM, 3384 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD, 'LAMI' AS NECK_CD, '201707' AS YYYYMM, 3420 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD, 'TABB' AS NECK_CD, '201708' AS YYYYMM, 3283.2 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0002' AS ITEM_CD, 'LAMI' AS NECK_CD, '201708' AS YYYYMM, 3456 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD, 'TABB' AS NECK_CD, '201707' AS YYYYMM, 3384 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD, 'LAMI' AS NECK_CD, '201707' AS YYYYMM, 3420 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD, 'TABB' AS NECK_CD, '201708' AS YYYYMM, 3283.2 AS CAPA_QTY FROM DUAL UNION ALL
SELECT 'L2' AS LINE_CD, 'ITEM_0004' AS ITEM_CD, 'LAMI' AS NECK_CD, '201708' AS YYYYMM, 3456 AS CAPA_QTY FROM DUAL
), CALD_MST AS (
SELECT '20170727' AS CALD_DT, '201730' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170728' AS CALD_DT, '201730' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170729' AS CALD_DT, '201730' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170730' AS CALD_DT, '201731A' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170731' AS CALD_DT, '201731A' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170801' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170802' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170803' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170804' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170805' AS CALD_DT, '201731B' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170806' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170807' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170808' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170809' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170810' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170811' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170812' AS CALD_DT, '201732' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170813' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170814' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170815' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170816' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170817' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170818' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170819' AS CALD_DT, '201733' AS YYYY_PART_WK_CD FROM DUAL UNION ALL
SELECT '20170820' AS CALD_DT, '201734' AS YYYY_PART_WK_CD FROM DUAL
), SUB1 AS(
SELECT CIP.LINE_CD
, CIP.NECK_CD
, CIP.ITEM_CD
, CS.SO_QTY
, CM.CALD_DT
, CIP.YYYYMM
, CM.YYYY_PART_WK_CD
, CIP.CAPA_QTY
FROM CO_ITEM_PROD CIP
, CO_SO CS
, CALD_MST CM
WHERE CIP.ITEM_CD = CS.ITEM_CD
AND CIP.YYYYMM = SUBSTRING(CM.CALD_DT, 1, 6)
)
SELECT SUB3.LINE_CD
, SUB3.NECK_CD
, SUB3.ITEM_CD
, SUB3.SO_QTY
, SUB3.CALD_DT
, SUB3.YYYY_PART_WK_CD
, SUB3.CAPA_QTY
, CASE WHEN SUB3.SO_QTY > SUB3.SO_CAPA_QTY
THEN SUB3.CAPA_QTY
ELSE SUB3.CAPA_QTY - (SUB3.SO_CAPA_QTY - SUB3.SO_QTY) END USE_CAPA_QTY
, CASE WHEN SUB3.SO_QTY > SUB3.SO_CAPA_QTY
THEN SUB3.SO_CAPA_QTY
ELSE SUB3.SO_QTY END SO_CAPA_QTY
, CASE WHEN SUB3.SO_QTY > SUB3.SO_CAPA_QTY
THEN 0
ELSE SUB3.SO_CAPA_QTY - SUB3.SO_QTY END REMAIN_QTY
FROM (
SELECT SUB1.LINE_CD
, SUB1.NECK_CD
, SUB1.ITEM_CD
, SUB1.SO_QTY
, SUB1.CALD_DT
, SUB1.YYYY_PART_WK_CD
, SUB1.CAPA_QTY
, (SELECT SUM(SUB2.CAPA_QTY)
FROM SUB1 SUB2
WHERE SUB2.LINE_CD = SUB1.LINE_CD
AND SUB2.NECK_CD = SUB1.NECK_CD
AND SUB2.ITEM_CD = SUB1.ITEM_CD
AND SUB2.CALD_DT <= SUB1.CALD_DT
HAVING SUM(SUB2.CAPA_QTY) <= SUB1.SO_QTY + SUB1.CAPA_QTY
) SO_CAPA_QTY
FROM SUB1
) SUB3
WHERE SUB3.SO_CAPA_QTY IS NOT NULL
ORDER BY SUB3.LINE_CD
, SUB3.NECK_CD
, SUB3.ITEM_CD
, SUB3.CALD_DT
, SUB3.CAPA_QTY
by 잭키올
[2017.06.26 15:01:51]
감사합니다.. 하지만 제가 원하는 결과가 아니군요.. ㅜㅜ
by 마농
[2017.06.27 16:20:24]
WITH co_line_mst AS
(
SELECT 'L1' line_cd, 'ITEM_0001' item_cd FROM dual
UNION ALL SELECT 'L2', 'ITEM_0002' FROM dual
UNION ALL SELECT 'L1', 'ITEM_0003' FROM dual
UNION ALL SELECT 'L2', 'ITEM_0004' FROM dual
)
, co_so AS
(
SELECT 'ITEM_0001' item_cd, '20170710' rdd_dt, 20000 so_qty FROM dual
UNION ALL SELECT 'ITEM_0002', '20170810', 20000 FROM dual
UNION ALL SELECT 'ITEM_0003', '20170820', 25000 FROM dual
UNION ALL SELECT 'ITEM_0004', '20170820', 15000 FROM dual
)
, co_item_prod AS
(
SELECT 'L1' line_cd, 'ITEM_0001' item_cd, 'TABB' neck_cd, '201707' yyyymm, 3456 capa_qty FROM dual
UNION ALL SELECT 'L1', 'ITEM_0001', 'LAMI', '201707', 3214.08 FROM dual
UNION ALL SELECT 'L1', 'ITEM_0001', 'TABB', '201708', 3456 FROM dual
UNION ALL SELECT 'L1', 'ITEM_0001', 'LAMI', '201708', 3110.4 FROM dual
UNION ALL SELECT 'L1', 'ITEM_0003', 'TABB', '201707', 3384 FROM dual
UNION ALL SELECT 'L1', 'ITEM_0003', 'LAMI', '201707', 3420 FROM dual
UNION ALL SELECT 'L1', 'ITEM_0003', 'TABB', '201708', 3283.2 FROM dual
UNION ALL SELECT 'L1', 'ITEM_0003', 'LAMI', '201708', 3456 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0002', 'TABB', '201707', 3384 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0002', 'LAMI', '201707', 3420 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0002', 'TABB', '201708', 3283.2 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0002', 'LAMI', '201708', 3456 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0004', 'TABB', '201707', 3384 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0004', 'LAMI', '201707', 3420 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0004', 'TABB', '201708', 3283.2 FROM dual
UNION ALL SELECT 'L2', 'ITEM_0004', 'LAMI', '201708', 3456 FROM dual
)
, cald_mst AS
(
SELECT '20170727' cald_dt, '201730' yyyy_part_wk_cd FROM dual
UNION ALL SELECT '20170728', '201730' FROM dual
UNION ALL SELECT '20170729', '201730' FROM dual
UNION ALL SELECT '20170730', '201731A' 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', '201732' 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', '201733' FROM dual
UNION ALL SELECT '20170814', '201733' FROM dual
UNION ALL SELECT '20170815', '201733' FROM dual
UNION ALL SELECT '20170816', '201733' FROM dual
UNION ALL SELECT '20170817', '201733' FROM dual
UNION ALL SELECT '20170818', '201733' FROM dual
UNION ALL SELECT '20170819', '201733' FROM dual
UNION ALL SELECT '20170820', '201734' FROM dual
)
, t1 AS
(
SELECT b.line_cd
, a.item_cd
, a.rdd_dt
, a.so_qty
, ROW_NUMBER() OVER(PARTITION BY b.line_cd ORDER BY a.rdd_dt) rn
FROM co_so a
, co_line_mst b
WHERE a.item_cd = b.item_cd
)
, t2 AS
(
SELECT cald_dt, yyyy_part_wk_cd
, ROW_NUMBER() OVER(ORDER BY cald_dt) dt
FROM cald_mst
WHERE cald_dt >= '20170727'
)
, t3 AS
(
SELECT line_cd, item_cd, yyyymm
, MIN(neck_cd) KEEP(DENSE_RANK FIRST ORDER BY capa_qty) neck_cd
, MIN(capa_qty) capa_qty
FROM co_item_prod
GROUP BY line_cd, item_cd, yyyymm
ORDER BY line_cd, item_cd, yyyymm
)
, t4(line_cd, neck_cd, item_cd, so_qty, cald_dt, yyyy_part_wk_cd, capa_qty, use_capa_qty, so_capa_qty, remain_qty, remain_so_qty, remain_rat, rn, dt) AS
(
SELECT a.line_cd
, c.neck_cd
, a.item_cd
, a.so_qty
, b.cald_dt
, b.yyyy_part_wk_cd
, c.capa_qty
, LEAST(a.so_qty, c.capa_qty) use_capa_qty
, LEAST(a.so_qty, c.capa_qty) so_capa_qty
, c.capa_qty - LEAST(a.so_qty, c.capa_qty) remain_qty
, a.so_qty - LEAST(a.so_qty, c.capa_qty) remain_so_qty
, (c.capa_qty - LEAST(a.so_qty, c.capa_qty)) / c.capa_qty remain_rat
, a.rn
, b.dt
FROM t1 a
, t2 b
, t3 c
WHERE c.line_cd = a.line_cd
AND c.item_cd = a.item_cd
AND c.yyyymm = SUBSTR(b.cald_dt, 1, 6)
AND a.rn = 1
AND b.dt = 1
UNION ALL
SELECT a.line_cd
, c.neck_cd
, a.item_cd
, a.so_qty
, b.cald_dt
, b.yyyy_part_wk_cd
, c.capa_qty
, LEAST( DECODE(d.rn, a.rn, d.remain_so_qty, a.so_qty)
, DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1)) * c.capa_qty
) use_capa_qty
, DECODE(d.rn, a.rn, d.so_capa_qty, 0)
+ LEAST( DECODE(d.rn, a.rn, d.remain_so_qty, a.so_qty)
, DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1)) * c.capa_qty
) so_capa_qty
, DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1)) * c.capa_qty
- LEAST( DECODE(d.rn, a.rn, d.remain_so_qty, a.so_qty)
, DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1)) * c.capa_qty
) remain_qty
, DECODE(d.rn, a.rn, d.remain_so_qty, a.so_qty)
- LEAST( DECODE(d.rn, a.rn, d.remain_so_qty, a.so_qty)
, DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1)) * c.capa_qty
) remain_so_qty
, DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1))
* ( DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1)) * c.capa_qty
- LEAST( DECODE(d.rn, a.rn, d.remain_so_qty, a.so_qty)
, DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1)) * c.capa_qty
)
)
/ (DECODE(d.rn, a.rn, 1, DECODE(d.dt, b.dt, d.remain_rat, 1)) * c.capa_qty) AS remain_rat
, a.rn
, b.dt
FROM t1 a
, t2 b
, t3 c
, t4 d
WHERE c.line_cd = a.line_cd
AND c.item_cd = a.item_cd
AND c.yyyymm = SUBSTR(b.cald_dt, 1, 6)
AND a.rn = d.rn + DECODE(d.so_qty, d.so_capa_qty, 1, 0)
AND b.dt = d.dt + DECODE(d.remain_qty, 0, 1, 0)
AND a.line_cd = d.line_cd
)
SELECT line_cd
, neck_cd
, item_cd
, so_qty
, cald_dt
, yyyy_part_wk_cd
, capa_qty
, ROUND(use_capa_qty, 6) use_capa_qty
, ROUND(so_capa_qty , 6) so_capa_qty
, ROUND(remain_qty , 6) remain_qty
, ROUND( remain_rat
* LEAD(capa_qty) OVER(PARTITION BY line_cd ORDER BY item_cd, cald_dt)
, 6) remain_conv_qty
FROM t4
ORDER BY line_cd, item_cd, cald_dt
;
by 잭키올
[2017.06.27 19:29:00]
마농님 엑셀로 데이터 그려나가는 과정을 보고 싶습니다..
by 마농
[2017.06.28 09:15:47]
엑셀로 데이터 그려나가는 과정이라...
그건 질문하기 전에 이미 다 정해진 로직 아닌가요?
by 잭키올
[2017.06.28 10:03:22]
사람이 하는 단순 결과를 Copy & Paste 해 가면서 그려나가는 과정은 쉬운데 SQL 의 정해진 문법으로 로직을 그려나가는 과정이 너무 어렵더군요. 제가 생각하는 건 셀 하나의 값을 일일히 변수에 담아 가면서 하는 로직으로 생각했는데.. 그것은 쿼리스럽지 못하고 생각만큼 잘 안되더군요.. 마농님처럼 생각하는 과정을 키우기 위해서 여쭤보았습니다.
by 마농
[2017.06.28 10:43:52]
1. 계층쿼리의 준비 과정으로 t1, t2, t3 를 미리 정의합니다.
- t1 에서는 아이템의 처리순서 RN 을 구했구요.
- t2 에서는 달력의 필요부분을 가져와 순번 DT 를 구했습니다.
- t3 에서는 라인과 아이템에 대해서 여러 CAPA 값중 최소 CAPA 를 구했습니다.
2. 위에서 준비한 3개 집합을 조인합니다.
- 라인, 아이템, 필요수량(so_qty), 일자별가능수량(capa_qty)
3. 우선 계층쿼리의 시작조건은
- 라인별 아이템 우선순위 rn = 1
- 첫번째 일자조건 dt = 1 입니다.
4. 계층 쿼리 전개 조건은
- 선행 라인과 후행 라인이 같고 a.line_cd(후행) = d.line_cd(선행)
- 생산량을 모두 채우면 다음 아이템 못채우면 기존 아이템 (a.rn = d.rn + DECODE(d.so_qty, d.so_capa_qty, 1, 0))
- 생산량을 모두 채우고 가능수량이 남아 있으면 일자 유지, 아니면 다음날. (b.dt = d.dt + DECODE(d.remain_qty, 0, 1, 0))
5. SELECT 절에서 필요한 항목들을 정의 합니다.
- 생산요구량(so) 과 일별생산가능량(capa) 중 작은값이 실제 생산량(use)이 되구요
- 남은가능수량(remain_qty)는 capa - use 가 됩니다.
- 여기서 capa 는 아이템이 유지되는 순간에는 그냥 capa 이고
아이템이 바뀌는 순간에는 기존 남은 수량이 있는 경우와 아닌 경우로 나뉘는데
기존 남은 수량이 없으면 기냥 새로은 capa 가 되고
기존 남은 수량이 있으면 남은 수량을 남은 비율(remain_rat)로 환산하고
환산된 비율에 새로운 capa 를 곱해서 처리합니다.
그런데 여기소 또 고려할 사항이 있는게...
하루에 두개 아이템이 처리되는 경우만 있는게 아니라
생산요구량이 적은 경우에는 하루에 여러개 아이템이 처리될 수도 있으므로
남은 비율은 누적해서 곱해져야 합니다.
예를 들어 1번 아이템이 50% 처리했고 2번 아이템이 남은 카파중 또 50% 처리했으면
다음 3번 아이템은 50% * 50% = 25% 가 남은 상태로 처리해야 하는거죠.
상당히 복잡하네요.
한번에 생각해낸 것은 아니고 계속해서 시행착오를 거치면서 수정해 나간 것입니다.
) 버튼을 클릭하여 작성 하시면 됩니다.