데이터베이스 질문과 답변
| CODE_N | TYPE | NO_P | F_DATE | T_DATE | 원하는 M_DATE | M_date | M_WEEK | 원하는 M_week | USE | _QTY 비고 | |
| lg_sam | A | Apple | 2018-03-09 | 2019-04-08 | 2019-04-26 | 2019-04-15 | 2019-04-19 | 2019-04-18 | 147 | ||
| lg_sam | A | Grape | 2018-03-09 | 2019-04-08 | 2019-04-26 | 2019-04-22 | 2019-04-19 | 2019-04-19 | 147 | ||
| lg_sam | A | Lemon | 2018-03-09 | 2019-04-08 | 2019-04-26 | 2019-04-22 | 2019-04-19 | 2019-04-19 | 130 | ||
| lg_sam | A | Lemon | 2018-03-09 | 2019-04-08 | 2019-04-29 | 2019-04-29 | 2019-04-20 | 2019-04-20 | 17 | ||
| lg_sam | A | Snacks | 2018-03-09 | 2019-04-08 | 2019-04-29 | 2019-04-29 | 2019-04-20 | 2019-04-20 | 9 | ||
| lg_sam | A | Potato | 2018-03-09 | 2019-06-17 | 2019-06-17 | 2019-04-29 | 2019-06-29 | 2019-04-20 | 102 | ||
| lg_sam | A | Potato | 2018-03-09 | 2019-06-17 | 2019-06-17 | 2019-06-17 | 2019-06-29 | 2019-06-29 | 45 | 조건값을채우고 나머지 값은 그대로 출력 | |
| lg_sam | A | Berry | 2019-03-09 | 2019-08-01 | 2019-08-01 | 2019-08-01 | 2019-08-36 | 2019-08-36 | 60 | 그대로 출력 | |
| lg_sam | A | Bread | 2019-03-09 | 2019-09-26 | 2019-09-26 | 2019-09-26 | 2019-09-45 | 2019-09-45 | 60 | 그대로 출력 |
위 표는 결과값입니다.
하기 쿼리입니다.
-----------------------------------------------------------------------
현재 min을 사용하여 최소값으로 조건쿼리가 원본을 커버하면 min으로 나오도록 하였습니다.
위 표에 비교란에 나머지값 그대로출력과, 그대로 출력을 뽑지 못하고있습니다.
또 한. type이 null인 경우(원본, 조건쿼리 둘다) 해당쿼리로 추출하지 못하여
Union을 사용하여 type is null / type is not null로 union사용중입니다.
조건데이터를
WITH t AS ( ---원본 SELECT 'lg_sam' code_n, 'Apple' no_p, '20180309' f_date,'20190408't_date, '20190426' m_date, '20190419' m_week, 147 qty, 'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, 'Lemon' no_p, '20180309' f_date,'20190408't_date, '20190426' m_date, '20190419' m_week, 147 qty, 'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, 'Grape' no_p, '20180309' f_date,'20190408't_date, '20190426' m_date, '20190419' m_week, 147 qty, 'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, 'Snacks' no_p, '20180309' f_date,'20190408't_date, '20190426' m_date, '20190419' m_week, 9 qty, 'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, 'Tomato' no_p, '20180309' f_date,'20190617't_date, '20190617' m_date, '20190629' m_week, 147 qty, 'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, 'Potato' no_p, '20180309' f_date,'20190617't_date, '20190617' m_date, '20190629' m_week, 483 qty, 'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, 'Berry' no_p, '20180309' f_date,'20190801't_date, '20190801' m_date, '20190836' m_week, 60 qty, 'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, 'Bread' no_p, '20180309' f_date,'20190926't_date, '20190926' m_date, '20190945' m_week, 60 qty, 'A' type FROM dual ) , c AS ( ---조건 SELECT 'lg_sam' code_n, '20190416' m_week, 38 qty,'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, '20190417' m_week, 98 qty,'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, '20190418' m_week, 144 qty,'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, '20190419' m_week, 144 qty,'A' type FROM dual UNION ALL SELECT 'lg_sam' code_n, '20190420' m_week, 128 qty,'A' type FROM dual ) , calendar AS ( ------------------4월 SELECT '20190401' d_date, '20190416' d_week, '2019' s_year, '04's_month, '16' s_week FROM dual union all SELECT '20190402' d_date, '20190416' d_week, '2019' s_year, '04's_month, '16' s_week FROM dual union all SELECT '20190403' d_date, '20190416' d_week, '2019' s_year, '04's_month, '16' s_week FROM dual union all SELECT '20190404' d_date, '20190416' d_week, '2019' s_year, '04's_month, '16' s_week FROM dual union all SELECT '20190405' d_date, '20190416' d_week, '2019' s_year, '04's_month, '16' s_week FROM dual union all SELECT '20190406' d_date, '20190416' d_week, '2019' s_year, '04's_month, '16' s_week FROM dual union all SELECT '20190407' d_date, '20190416' d_week, '2019' s_year, '04's_month, '16' s_week FROM dual union all SELECT '20190408' d_date, '20190417' d_week, '2019' s_year, '04's_month, '17' s_week FROM dual union all SELECT '20190409' d_date, '20190417' d_week, '2019' s_year, '04's_month, '17' s_week FROM dual union all SELECT '20190410' d_date, '20190417' d_week, '2019' s_year, '04's_month, '17' s_week FROM dual union all SELECT '20190411' d_date, '20190417' d_week, '2019' s_year, '04's_month, '17' s_week FROM dual union all SELECT '20190412' d_date, '20190417' d_week, '2019' s_year, '04's_month, '17' s_week FROM dual union all SELECT '20190413' d_date, '20190417' d_week, '2019' s_year, '04's_month, '17' s_week FROM dual union all SELECT '20190414' d_date, '20190417' d_week, '2019' s_year, '04's_month, '17' s_week FROM dual union all SELECT '20190415' d_date, '20190418' d_week, '2019' s_year, '04's_month, '18' s_week FROM dual union all SELECT '20190416' d_date, '20190418' d_week, '2019' s_year, '04's_month, '18' s_week FROM dual union all SELECT '20190417' d_date, '20190418' d_week, '2019' s_year, '04's_month, '18' s_week FROM dual union all SELECT '20190418' d_date, '20190418' d_week, '2019' s_year, '04's_month, '18' s_week FROM dual union all SELECT '20190419' d_date, '20190418' d_week, '2019' s_year, '04's_month, '18' s_week FROM dual union all SELECT '20190420' d_date, '20190418' d_week, '2019' s_year, '04's_month, '18' s_week FROM dual union all SELECT '20190421' d_date, '20190418' d_week, '2019' s_year, '04's_month, '18' s_week FROM dual union all SELECT '20190422' d_date, '20190419' d_week, '2019' s_year, '04's_month, '19' s_week FROM dual union all SELECT '20190423' d_date, '20190419' d_week, '2019' s_year, '04's_month, '19' s_week FROM dual union all SELECT '20190424' d_date, '20190419' d_week, '2019' s_year, '04's_month, '19' s_week FROM dual union all SELECT '20190425' d_date, '20190419' d_week, '2019' s_year, '04's_month, '19' s_week FROM dual union all SELECT '20190426' d_date, '20190419' d_week, '2019' s_year, '04's_month, '19' s_week FROM dual union all SELECT '20190427' d_date, '20190419' d_week, '2019' s_year, '04's_month, '19' s_week FROM dual union all SELECT '20190428' d_date, '20190419' d_week, '2019' s_year, '04's_month, '19' s_week FROM dual union all SELECT '20190429' d_date, '20190420' d_week, '2019' s_year, '04's_month, '20' s_week FROM dual union all SELECT '20190430' d_date, '20190420' d_week, '2019' s_year, '04's_month, '20' s_week FROM dual union all ---------------6월 SELECT '20190601' d_date, '20190626' d_week, '2019' s_year, '06's_month, '26' s_week FROM dual union all SELECT '20190602' d_date, '20190626' d_week, '2019' s_year, '06's_month, '26' s_week FROM dual union all SELECT '20190603' d_date, '20190627' d_week, '2019' s_year, '06's_month, '27' s_week FROM dual union all SELECT '20190604' d_date, '20190627' d_week, '2019' s_year, '06's_month, '27' s_week FROM dual union all SELECT '20190605' d_date, '20190627' d_week, '2019' s_year, '06's_month, '27' s_week FROM dual union all SELECT '20190606' d_date, '20190627' d_week, '2019' s_year, '06's_month, '27' s_week FROM dual union all SELECT '20190607' d_date, '20190627' d_week, '2019' s_year, '06's_month, '27' s_week FROM dual union all SELECT '20190608' d_date, '20190627' d_week, '2019' s_year, '06's_month, '27' s_week FROM dual union all SELECT '20190609' d_date, '20190627' d_week, '2019' s_year, '06's_month, '27' s_week FROM dual union all SELECT '20190610' d_date, '20190628' d_week, '2019' s_year, '06's_month, '28' s_week FROM dual union all SELECT '20190611' d_date, '20190628' d_week, '2019' s_year, '06's_month, '28' s_week FROM dual union all SELECT '20190612' d_date, '20190628' d_week, '2019' s_year, '06's_month, '28' s_week FROM dual union all SELECT '20190613' d_date, '20190628' d_week, '2019' s_year, '06's_month, '28' s_week FROM dual union all SELECT '20190614' d_date, '20190628' d_week, '2019' s_year, '06's_month, '28' s_week FROM dual union all SELECT '20190615' d_date, '20190628' d_week, '2019' s_year, '06's_month, '28' s_week FROM dual union all SELECT '20190616' d_date, '20190628' d_week, '2019' s_year, '06's_month, '28' s_week FROM dual union all SELECT '20190617' d_date, '20190629' d_week, '2019' s_year, '06's_month, '29' s_week FROM dual union all SELECT '20190618' d_date, '20190629' d_week, '2019' s_year, '06's_month, '29' s_week FROM dual union all SELECT '20190619' d_date, '20190629' d_week, '2019' s_year, '06's_month, '29' s_week FROM dual union all SELECT '20190620' d_date, '20190629' d_week, '2019' s_year, '06's_month, '29' s_week FROM dual union all SELECT '20190621' d_date, '20190629' d_week, '2019' s_year, '06's_month, '29' s_week FROM dual union all SELECT '20190622' d_date, '20190629' d_week, '2019' s_year, '06's_month, '29' s_week FROM dual union all SELECT '20190623' d_date, '20190629' d_week, '2019' s_year, '06's_month, '29' s_week FROM dual union all SELECT '20190624' d_date, '20190630' d_week, '2019' s_year, '06's_month, '30' s_week FROM dual union all SELECT '20190625' d_date, '20190630' d_week, '2019' s_year, '06's_month, '30' s_week FROM dual union all SELECT '20190626' d_date, '20190630' d_week, '2019' s_year, '06's_month, '30' s_week FROM dual union all SELECT '20190627' d_date, '20190630' d_week, '2019' s_year, '06's_month, '30' s_week FROM dual union all SELECT '20190628' d_date, '20190630' d_week, '2019' s_year, '06's_month, '30' s_week FROM dual union all SELECT '20190629' d_date, '20190630' d_week, '2019' s_year, '06's_month, '30' s_week FROM dual union all SELECT '20190630' d_date, '20190630' d_week, '2019' s_year, '06's_month, '30' s_week FROM dual union all SELECT '20190801' d_date, '20190834' d_week, '2019' s_year, '08's_month, '34' s_week FROM dual union all SELECT '20190926' d_date, '20190945' d_week, '2019' s_year, '09's_month, '45' s_week FROM dual )
by 마농
[2019.04.26 09:23:31]
예전 질문 내용을 제가 그대로 기억하고 있지는 않습니다.
기존 질문에 추가하듯이 질문하지 마시고, 새 질문 하실 때는 새롭게 설명해 주세요.
결과도 이상하고
원본 대비 결과가 맞나요? 수치가 뭔가 뭔가 안맞는 듯 한데요?
결과에 소수점이 왜 나오며?
Tomato 는 어디로 사라진거죠?
by 마농
[2019.04.26 14:50:55]
WITH t AS
(
---원본
SELECT 'lg_sam' code_n, 'Apple' no_p, '20180309' f_date, '20190408' t_date, '20190426' m_date, '20190419' m_week, 147 qty, 'A' type FROM dual
UNION ALL SELECT 'lg_sam', 'Grape' , '20180309', '20190408', '20190426', '20190419', 147, 'A' FROM dual
UNION ALL SELECT 'lg_sam', 'Lemon' , '20180309', '20190408', '20190426', '20190419', 147, 'A' FROM dual
UNION ALL SELECT 'lg_sam', 'Snacks', '20180309', '20190408', '20190426', '20190419', 9, 'A' FROM dual
UNION ALL SELECT 'lg_sam', 'Potato', '20180309', '20190617', '20190617', '20190629', 483, 'A' FROM dual
UNION ALL SELECT 'lg_sam', 'Tomato', '20180309', '20190617', '20190617', '20190629', 147, 'A' FROM dual
UNION ALL SELECT 'lg_sam', 'Berry' , '20180309', '20190801', '20190801', '20190836', 60, 'A' FROM dual
UNION ALL SELECT 'lg_sam', 'Bread' , '20180309', '20190926', '20190926', '20190945', 60, 'A' FROM dual
)
, c AS
(
---조건
SELECT 'lg_sam' code_n, '20190416' m_week, 38 qty,'A' type FROM dual
UNION ALL SELECT 'lg_sam', '20190417', 98, 'A' FROM dual
UNION ALL SELECT 'lg_sam', '20190418', 144, 'A' FROM dual
UNION ALL SELECT 'lg_sam', '20190419', 144, 'A' FROM dual
UNION ALL SELECT 'lg_sam', '20190420', 128, 'A' FROM dual
)
, calendar AS
(
-- 4월 --
SELECT '20190401' d_date, '20190416' d_week, '2019' s_year, '04's_month, '16' s_week FROM dual
UNION ALL SELECT '20190402', '20190416', '2019', '04', '16' FROM dual
UNION ALL SELECT '20190403', '20190416', '2019', '04', '16' FROM dual
UNION ALL SELECT '20190404', '20190416', '2019', '04', '16' FROM dual
UNION ALL SELECT '20190405', '20190416', '2019', '04', '16' FROM dual
UNION ALL SELECT '20190406', '20190416', '2019', '04', '16' FROM dual
UNION ALL SELECT '20190407', '20190416', '2019', '04', '16' FROM dual
UNION ALL SELECT '20190408', '20190417', '2019', '04', '17' FROM dual
UNION ALL SELECT '20190409', '20190417', '2019', '04', '17' FROM dual
UNION ALL SELECT '20190410', '20190417', '2019', '04', '17' FROM dual
UNION ALL SELECT '20190411', '20190417', '2019', '04', '17' FROM dual
UNION ALL SELECT '20190412', '20190417', '2019', '04', '17' FROM dual
UNION ALL SELECT '20190413', '20190417', '2019', '04', '17' FROM dual
UNION ALL SELECT '20190414', '20190417', '2019', '04', '17' FROM dual
UNION ALL SELECT '20190415', '20190418', '2019', '04', '18' FROM dual
UNION ALL SELECT '20190416', '20190418', '2019', '04', '18' FROM dual
UNION ALL SELECT '20190417', '20190418', '2019', '04', '18' FROM dual
UNION ALL SELECT '20190418', '20190418', '2019', '04', '18' FROM dual
UNION ALL SELECT '20190419', '20190418', '2019', '04', '18' FROM dual
UNION ALL SELECT '20190420', '20190418', '2019', '04', '18' FROM dual
UNION ALL SELECT '20190421', '20190418', '2019', '04', '18' FROM dual
UNION ALL SELECT '20190422', '20190419', '2019', '04', '19' FROM dual
UNION ALL SELECT '20190423', '20190419', '2019', '04', '19' FROM dual
UNION ALL SELECT '20190424', '20190419', '2019', '04', '19' FROM dual
UNION ALL SELECT '20190425', '20190419', '2019', '04', '19' FROM dual
UNION ALL SELECT '20190426', '20190419', '2019', '04', '19' FROM dual
UNION ALL SELECT '20190427', '20190419', '2019', '04', '19' FROM dual
UNION ALL SELECT '20190428', '20190419', '2019', '04', '19' FROM dual
UNION ALL SELECT '20190429', '20190420', '2019', '04', '20' FROM dual
UNION ALL SELECT '20190430', '20190420', '2019', '04', '20' FROM dual
-- 6월 --
UNION ALL SELECT '20190601', '20190626', '2019', '06', '26' FROM dual
UNION ALL SELECT '20190602', '20190626', '2019', '06', '26' FROM dual
UNION ALL SELECT '20190603', '20190627', '2019', '06', '27' FROM dual
UNION ALL SELECT '20190604', '20190627', '2019', '06', '27' FROM dual
UNION ALL SELECT '20190605', '20190627', '2019', '06', '27' FROM dual
UNION ALL SELECT '20190606', '20190627', '2019', '06', '27' FROM dual
UNION ALL SELECT '20190607', '20190627', '2019', '06', '27' FROM dual
UNION ALL SELECT '20190608', '20190627', '2019', '06', '27' FROM dual
UNION ALL SELECT '20190609', '20190627', '2019', '06', '27' FROM dual
UNION ALL SELECT '20190610', '20190628', '2019', '06', '28' FROM dual
UNION ALL SELECT '20190611', '20190628', '2019', '06', '28' FROM dual
UNION ALL SELECT '20190612', '20190628', '2019', '06', '28' FROM dual
UNION ALL SELECT '20190613', '20190628', '2019', '06', '28' FROM dual
UNION ALL SELECT '20190614', '20190628', '2019', '06', '28' FROM dual
UNION ALL SELECT '20190615', '20190628', '2019', '06', '28' FROM dual
UNION ALL SELECT '20190616', '20190628', '2019', '06', '28' FROM dual
UNION ALL SELECT '20190617', '20190629', '2019', '06', '29' FROM dual
UNION ALL SELECT '20190618', '20190629', '2019', '06', '29' FROM dual
UNION ALL SELECT '20190619', '20190629', '2019', '06', '29' FROM dual
UNION ALL SELECT '20190620', '20190629', '2019', '06', '29' FROM dual
UNION ALL SELECT '20190621', '20190629', '2019', '06', '29' FROM dual
UNION ALL SELECT '20190622', '20190629', '2019', '06', '29' FROM dual
UNION ALL SELECT '20190623', '20190629', '2019', '06', '29' FROM dual
UNION ALL SELECT '20190624', '20190630', '2019', '06', '30' FROM dual
UNION ALL SELECT '20190625', '20190630', '2019', '06', '30' FROM dual
UNION ALL SELECT '20190626', '20190630', '2019', '06', '30' FROM dual
UNION ALL SELECT '20190627', '20190630', '2019', '06', '30' FROM dual
UNION ALL SELECT '20190628', '20190630', '2019', '06', '30' FROM dual
UNION ALL SELECT '20190629', '20190630', '2019', '06', '30' FROM dual
UNION ALL SELECT '20190630', '20190630', '2019', '06', '30' FROM dual
-- 8월 --
UNION ALL SELECT '20190801', '20190834', '2019', '08', '34' FROM dual
UNION ALL SELECT '20190801', '20190836', '2019', '08', '36' FROM dual
-- 9월 --
UNION ALL SELECT '20190926', '20190945', '2019', '09', '45' FROM dual
)
SELECT a.code_n
, a.type
, a.no_p
, a.f_date
, a.t_date
, DECODE(gb, 'OLD', a.m_date, c.d_date) m_date
, a.m_week
, DECODE(d.lv, 1, a.gb, 'NORMAL') gb
, CASE WHEN a.gb = 'NORMAL' OR d.lv = 2 THEN a.rem_qty ELSE a.use_qty END use_qty
FROM (SELECT code_n
, type
, no_p
, f_date
, t_date
, m_date
, gb
, DECODE(gb, 'OLD', MAX(b_w), m_week) m_week
, SUM(use_qty) use_qty
, qty - SUM(SUM(use_qty)) OVER(PARTITION BY code_n, type, no_p) rem_qty
FROM (SELECT a.code_n, a.type, a.no_p, a.f_date, a.t_date, a.m_date
, CASE WHEN a.m_week < b.m_week THEN 'NEW'
WHEN a.m_week >= b.m_week THEN 'OLD'
ELSE 'NORMAL'
END gb
, b.m_week b_w
, CASE WHEN a.m_week < b.m_week THEN b.m_week ELSE a.m_week END m_week
, a.qty
, NVL(LEAST(a.s_qty - b.s_qty + b.qty, b.s_qty - a.s_qty + a.qty, a.qty, b.qty), 0) use_qty
FROM (SELECT code_n, type, no_p, f_date, t_date, m_date, m_week, qty
, SUM(qty) OVER(PARTITION BY code_n, type ORDER BY m_date, f_date, no_p) s_qty
FROM t
) a
LEFT OUTER JOIN
(SELECT code_n, type, m_week, qty
, SUM(qty) OVER(PARTITION BY code_n, type ORDER BY m_week) s_qty
FROM c
) b
ON a.code_n = b.code_n
AND a.type = b.type
AND a.s_qty > b.s_qty - b.qty
AND b.s_qty > a.s_qty - a.qty
)
GROUP BY code_n, type, f_date, t_date, no_p, gb, m_date, m_week, qty
) a
, (SELECT d_date, s_year, s_week
, ROW_NUMBER() OVER(PARTITION BY s_year, s_week ORDER BY d_date) rn
FROM calendar
) c
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) d
WHERE a.m_week LIKE c.s_year||'__'||c.s_week
AND c.rn = 1
AND d.lv <= CASE WHEN a.gb != 'NORMAL' AND a.rem_qty > 0 THEN 2 ELSE 1 END
ORDER BY code_n, type, f_date, t_date, no_p, gb DESC
;
by 캘린다
[2019.05.09 09:21:25]
알려주신 함수로 결과값을 보고있다가
NORMAL에서 2176이 원래는 7424값이 나와야하는데 2176이 나오고있습니다.
이 외에도 code_n이 마지막 커버가 되고 원본값이 남는경우 표기되지않거나 이상하게출력됨.
WITH t AS
(
---원본
select 'lg_sam' code_n, 'appale' no_p, '20190429' f_date, '20190711' t_date, '20190711' m_date, '20190732' m_week, '12000' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'grape' no_p, '20190429' f_date, '20190718' t_date, '20190718' m_date, '20190733' m_week, '12000' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'lemon' no_p, '20190429' f_date, '20190725' t_date, '20190725' m_date, '20190734' m_week, '12000' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'snacks' no_p, '20190228' f_date, '20190509' t_date, '20190512' m_date, '20190522' m_week, '16486' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'potato' no_p, '20190228' f_date, '20190501' t_date, '20190512' m_date, '20190522' m_week, '1500' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'berry' no_p, '20190228' f_date, '20190516' t_date, '20190516' m_date, '20190523' m_week, '5910' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'berry' no_p, '20190228' f_date, '20190516' t_date, '20190523' m_date, '20190524' m_week, '7090' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'bread' no_p, '20190329' f_date, '20190516' t_date, '20190523' m_date, '20190524' m_week, '20' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'bread' no_p, '20190329' f_date, '20190516' t_date, '20190525' m_date, '20190524' m_week, '980' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'tomato' no_p, '20190228' f_date, '20190601' t_date, '20190601' m_date, '20190626' m_week, '1500' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'balm' no_p, '20190329' f_date, '20190605' t_date, '20190605' m_date, '20190627' m_week, '2082' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'ring' no_p, '20190329' f_date, '20190613' t_date, '20190615' m_date, '20190628' m_week, '469' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'balm' no_p, '20190329' f_date, '20190605' t_date, '20190615' m_date, '20190628' m_week, '13918' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'co' no_p, '20190329' f_date, '20190620' t_date, '20190623' m_date, '20190629' m_week, '1434' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'ring' no_p, '20190329' f_date, '20190613' t_date, '20190623' m_date, '20190629' m_week, '15531' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'co' no_p, '20190329' f_date, '20190620' t_date, '20190702' m_date, '20190731' m_week, '14566' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'rim' no_p, '20190429' f_date, '20190704' t_date, '20190704' m_date, '20190731' m_week, '5248' qty, 'NULL' type From Dual union all
select 'lg_sam' code_n, 'rim' no_p, '20190429' f_date, '20190704' t_date, '20190709' m_date, '20190732' m_week, '7752' qty, 'NULL' type From Dual
)
, c AS
(
---조건
select 'lg_sam' code_n, '20190416' m_week, '11128' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190417' m_week, '6767' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190418' m_week, '8646' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190419' m_week, '10087' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190420' m_week, '5764' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190521' m_week, '10087' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190522' m_week, '10087' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190523' m_week, '10087' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190524' m_week, '7925' qty, 'NULL' type from Dual union all
select 'lg_sam' code_n, '20190525' m_week, '6484' qty, 'NULL' type from Dual
)
, calendar AS
(
select '20190301' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all
select '20190302' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all
select '20190303' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all
select '20190304' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all
select '20190305' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all
select '20190306' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all
select '20190307' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all
select '20190308' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all
select '20190309' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all
select '20190310' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all
select '20190311' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all
select '20190312' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all
select '20190313' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all
select '20190314' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all
select '20190315' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all
select '20190316' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all
select '20190317' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all
select '20190318' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all
select '20190319' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all
select '20190320' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all
select '20190321' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all
select '20190322' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all
select '20190323' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all
select '20190324' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all
select '20190325' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all
select '20190326' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all
select '20190327' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all
select '20190328' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all
select '20190329' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all
select '20190330' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all
select '20190331' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all
select '20190401' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all
select '20190402' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all
select '20190403' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all
select '20190404' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all
select '20190405' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all
select '20190406' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all
select '20190407' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all
select '20190408' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all
select '20190409' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all
select '20190410' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all
select '20190411' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all
select '20190412' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all
select '20190413' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all
select '20190414' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all
select '20190415' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all
select '20190416' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all
select '20190417' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all
select '20190418' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all
select '20190419' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all
select '20190420' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all
select '20190421' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all
select '20190422' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all
select '20190423' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all
select '20190424' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all
select '20190425' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all
select '20190426' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all
select '20190427' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all
select '20190428' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all
select '20190429' d_date, '20190420' d_week, '2019' s_year, '4' s_month, '20' s_week from Dual union all
select '20190430' d_date, '20190420' d_week, '2019' s_year, '4' s_month, '20' s_week from Dual union all
select '20190501' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all
select '20190502' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all
select '20190503' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all
select '20190504' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all
select '20190505' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all
select '20190506' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all
select '20190507' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all
select '20190508' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all
select '20190509' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all
select '20190510' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all
select '20190511' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all
select '20190512' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all
select '20190513' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all
select '20190514' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all
select '20190515' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all
select '20190516' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all
select '20190517' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all
select '20190518' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all
select '20190519' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all
select '20190520' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all
select '20190521' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all
select '20190522' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all
select '20190523' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all
select '20190524' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all
select '20190525' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all
select '20190526' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all
select '20190527' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all
select '20190528' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all
select '20190529' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all
select '20190530' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all
select '20190531' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all
select '20190601' d_date, '20190626' d_week, '2019' s_year, '6' s_month, '26' s_week from Dual union all
select '20190602' d_date, '20190626' d_week, '2019' s_year, '6' s_month, '26' s_week from Dual union all
select '20190603' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all
select '20190604' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all
select '20190605' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all
select '20190606' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all
select '20190607' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all
select '20190608' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all
select '20190609' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all
select '20190610' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all
select '20190611' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all
select '20190612' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all
select '20190613' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all
select '20190614' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all
select '20190615' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all
select '20190616' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all
select '20190617' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all
select '20190618' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all
select '20190619' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all
select '20190620' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all
select '20190621' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all
select '20190622' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all
select '20190623' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all
select '20190624' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all
select '20190625' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all
select '20190626' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all
select '20190627' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all
select '20190628' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all
select '20190629' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all
select '20190630' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all
select '20190701' d_date, '20190731' d_week, '2019' s_year, '7' s_month, '31' s_week from Dual union all
select '20190702' d_date, '20190731' d_week, '2019' s_year, '7' s_month, '31' s_week from Dual union all
select '20190703' d_date, '20190731' d_week, '2019' s_year, '7' s_month, '31' s_week from Dual union all
select '20190704' d_date, '20190731' d_week, '2019' s_year, '7' s_month, '31' s_week from Dual union all
select '20190705' d_date, '20190731' d_week, '2019' s_year, '7' s_month, '31' s_week from Dual union all
select '20190706' d_date, '20190731' d_week, '2019' s_year, '7' s_month, '31' s_week from Dual union all
select '20190707' d_date, '20190731' d_week, '2019' s_year, '7' s_month, '31' s_week from Dual union all
select '20190708' d_date, '20190732' d_week, '2019' s_year, '7' s_month, '32' s_week from Dual union all
select '20190709' d_date, '20190732' d_week, '2019' s_year, '7' s_month, '32' s_week from Dual union all
select '20190710' d_date, '20190732' d_week, '2019' s_year, '7' s_month, '32' s_week from Dual union all
select '20190711' d_date, '20190732' d_week, '2019' s_year, '7' s_month, '32' s_week from Dual union all
select '20190712' d_date, '20190732' d_week, '2019' s_year, '7' s_month, '32' s_week from Dual union all
select '20190713' d_date, '20190732' d_week, '2019' s_year, '7' s_month, '32' s_week from Dual union all
select '20190714' d_date, '20190732' d_week, '2019' s_year, '7' s_month, '32' s_week from Dual union all
select '20190715' d_date, '20190733' d_week, '2019' s_year, '7' s_month, '33' s_week from Dual union all
select '20190716' d_date, '20190733' d_week, '2019' s_year, '7' s_month, '33' s_week from Dual union all
select '20190717' d_date, '20190733' d_week, '2019' s_year, '7' s_month, '33' s_week from Dual union all
select '20190718' d_date, '20190733' d_week, '2019' s_year, '7' s_month, '33' s_week from Dual union all
select '20190719' d_date, '20190733' d_week, '2019' s_year, '7' s_month, '33' s_week from Dual union all
select '20190720' d_date, '20190733' d_week, '2019' s_year, '7' s_month, '33' s_week from Dual union all
select '20190721' d_date, '20190733' d_week, '2019' s_year, '7' s_month, '33' s_week from Dual union all
select '20190722' d_date, '20190734' d_week, '2019' s_year, '7' s_month, '34' s_week from Dual union all
select '20190723' d_date, '20190734' d_week, '2019' s_year, '7' s_month, '34' s_week from Dual union all
select '20190724' d_date, '20190734' d_week, '2019' s_year, '7' s_month, '34' s_week from Dual union all
select '20190725' d_date, '20190734' d_week, '2019' s_year, '7' s_month, '34' s_week from Dual union all
select '20190726' d_date, '20190734' d_week, '2019' s_year, '7' s_month, '34' s_week from Dual union all
select '20190727' d_date, '20190734' d_week, '2019' s_year, '7' s_month, '34' s_week from Dual union all
select '20190728' d_date, '20190734' d_week, '2019' s_year, '7' s_month, '34' s_week from Dual union all
select '20190729' d_date, '20190735' d_week, '2019' s_year, '7' s_month, '35' s_week from Dual union all
select '20190730' d_date, '20190735' d_week, '2019' s_year, '7' s_month, '35' s_week from Dual union all
select '20190731' d_date, '20190735' d_week, '2019' s_year, '7' s_month, '35' s_week from Dual
)
SELECT a.code_n
, a.type
, a.no_p
, a.f_date
, a.t_date
, DECODE(gb, 'OLD', a.m_date, c.d_date) m_date
, a.m_week
, DECODE(d.lv, 1, a.gb, 'NORMAL') gb
, CASE WHEN a.gb = 'NORMAL' OR d.lv = 2 THEN a.rem_qty ELSE a.use_qty END use_qty
FROM (SELECT code_n
, type
, no_p
, f_date
, t_date
, m_date
, gb
, DECODE(gb, 'OLD', MAX(b_w), m_week) m_week
, SUM(use_qty) use_qty
, qty - SUM(SUM(use_qty)) OVER(PARTITION BY code_n, type, no_p) rem_qty
FROM (SELECT a.code_n, a.type, a.no_p, a.f_date, a.t_date, a.m_date
, CASE WHEN a.m_week < b.m_week THEN 'NEW'
WHEN a.m_week >= b.m_week THEN 'OLD'
ELSE 'NORMAL'
END gb
, b.m_week b_w
, CASE WHEN a.m_week < b.m_week THEN b.m_week ELSE a.m_week END m_week
, a.qty
, NVL(LEAST(a.s_qty - b.s_qty + b.qty, b.s_qty - a.s_qty + a.qty, a.qty, b.qty), 0) use_qty
FROM (SELECT code_n, type, no_p, f_date, t_date, m_date, m_week, qty
, SUM(qty) OVER(PARTITION BY code_n, type ORDER BY m_date, f_date, no_p) s_qty
FROM t
) a
LEFT OUTER JOIN
(SELECT code_n, type, m_week, qty
, SUM(qty) OVER(PARTITION BY code_n, type ORDER BY m_week) s_qty
FROM c
) b
ON a.code_n = b.code_n
AND a.type = b.type
AND a.s_qty > b.s_qty - b.qty
AND b.s_qty > a.s_qty - a.qty
)
GROUP BY code_n, type, f_date, t_date, no_p, gb, m_date, m_week, qty
) a
, (SELECT d_date, s_year, s_week
, ROW_NUMBER() OVER(PARTITION BY s_year, s_week ORDER BY d_date) rn
FROM calendar
) c
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) d
WHERE a.m_week LIKE c.s_year||'__'||c.s_week
AND c.rn = 1
AND d.lv <= CASE WHEN a.gb != 'NORMAL' AND a.rem_qty > 0 THEN 2 ELSE 1 END
ORDER BY code_n, type, f_date, t_date, no_p, gb DESC
by 마농
[2019.05.09 10:46:59]
no_p 가 중복이 있네요.
기존 예시엔 중복이 없어서 no_p 를 기준으로 잡았었는데.
중복이 있으니 기준이 더 추가되어야 할 듯 하네요.
일단 qty 를 추가해 봤는데..실데이터 상황에 따라 추가가 더 필요할 수도 있겠네요.
- 수정전 : , qty - SUM(SUM(use_qty)) OVER(PARTITION BY code_n, type, no_p) rem_qty
- 수정후 : , qty - SUM(SUM(use_qty)) OVER(PARTITION BY code_n, type, no_p, qty) rem_qty
by 캘린다
[2019.05.09 11:38:58]
중복을 생각하지 못하였네요 하기 원본에서 50 조건을 중촉하고 나머지 값 new로 나오는데 수량차 22발생합니다 WITH t AS ( ---원본 select 'lg_sam' code_n, 'lemon' no_p, '20181210' f_date, '20190405' t_date, '20190512' m_date, '20190522' m_week, '40' qty, 'NULL' type From Dual union all select 'lg_sam' code_n, 'apple' no_p, '20181210' f_date, '20190405' t_date, '20190512' m_date, '20190522' m_week, '50' qty, 'NULL' type From Dual union all select 'lg_sam' code_n, 'lim' no_p, '20190227' f_date, '20190515' t_date, '20190515' m_date, '20190523' m_week, '36' qty, 'NULL' type From Dual union all select 'lg_sam' code_n, 'love' no_p, '20190320' f_date, '20190515' t_date, '20190515' m_date, '20190523' m_week, '29' qty, 'NULL' type From Dual union all select 'lg_sam' code_n, 'love' no_p, '20190320' f_date, '20190515' t_date, '20190521' m_date, '20190524' m_week, '7' qty, 'NULL' type From Dual union all select 'lg_sam' code_n, 'true' no_p, '20190227' f_date, '20190615' t_date, '20190615' m_date, '20190628' m_week, '36' qty, 'NULL' type From Dual union all select 'lg_sam' code_n, 'grape' no_p, '20190320' f_date, '20190615' t_date, '20190615' m_date, '20190628' m_week, '18' qty, 'NULL' type From Dual union all select 'lg_sam' code_n, 'beer' no_p, '20190227' f_date, '20190630' t_date, '20190630' m_date, '20190630' m_week, '50' qty, 'NULL' type From Dual ) , c AS ( ---조건 select 'lg_sam' code_n, '20190417' m_week, '28' qty, 'NULL' type from Dual union all select 'lg_sam' code_n, '20190419' m_week, '28' qty, 'NULL' type from Dual union all select 'lg_sam' code_n, '20190525' m_week, '12' qty, 'NULL' type from Dual ) , calendar AS ( select '20190201' d_date, '20190206' d_week, '2019' s_year, '2' s_month, '6' s_week from Dual union all select '20190202' d_date, '20190206' d_week, '2019' s_year, '2' s_month, '6' s_week from Dual union all select '20190203' d_date, '20190206' d_week, '2019' s_year, '2' s_month, '6' s_week from Dual union all select '20190204' d_date, '20190207' d_week, '2019' s_year, '2' s_month, '7' s_week from Dual union all select '20190205' d_date, '20190207' d_week, '2019' s_year, '2' s_month, '7' s_week from Dual union all select '20190206' d_date, '20190207' d_week, '2019' s_year, '2' s_month, '7' s_week from Dual union all select '20190207' d_date, '20190207' d_week, '2019' s_year, '2' s_month, '7' s_week from Dual union all select '20190208' d_date, '20190207' d_week, '2019' s_year, '2' s_month, '7' s_week from Dual union all select '20190209' d_date, '20190207' d_week, '2019' s_year, '2' s_month, '7' s_week from Dual union all select '20190210' d_date, '20190207' d_week, '2019' s_year, '2' s_month, '7' s_week from Dual union all select '20190211' d_date, '20190208' d_week, '2019' s_year, '2' s_month, '8' s_week from Dual union all select '20190212' d_date, '20190208' d_week, '2019' s_year, '2' s_month, '8' s_week from Dual union all select '20190213' d_date, '20190208' d_week, '2019' s_year, '2' s_month, '8' s_week from Dual union all select '20190214' d_date, '20190208' d_week, '2019' s_year, '2' s_month, '8' s_week from Dual union all select '20190215' d_date, '20190208' d_week, '2019' s_year, '2' s_month, '8' s_week from Dual union all select '20190216' d_date, '20190208' d_week, '2019' s_year, '2' s_month, '8' s_week from Dual union all select '20190217' d_date, '20190208' d_week, '2019' s_year, '2' s_month, '8' s_week from Dual union all select '20190218' d_date, '20190209' d_week, '2019' s_year, '2' s_month, '9' s_week from Dual union all select '20190219' d_date, '20190209' d_week, '2019' s_year, '2' s_month, '9' s_week from Dual union all select '20190220' d_date, '20190209' d_week, '2019' s_year, '2' s_month, '9' s_week from Dual union all select '20190221' d_date, '20190209' d_week, '2019' s_year, '2' s_month, '9' s_week from Dual union all select '20190222' d_date, '20190209' d_week, '2019' s_year, '2' s_month, '9' s_week from Dual union all select '20190223' d_date, '20190209' d_week, '2019' s_year, '2' s_month, '9' s_week from Dual union all select '20190224' d_date, '20190209' d_week, '2019' s_year, '2' s_month, '9' s_week from Dual union all select '20190225' d_date, '20190210' d_week, '2019' s_year, '2' s_month, '10' s_week from Dual union all select '20190226' d_date, '20190210' d_week, '2019' s_year, '2' s_month, '10' s_week from Dual union all select '20190227' d_date, '20190210' d_week, '2019' s_year, '2' s_month, '10' s_week from Dual union all select '20190228' d_date, '20190210' d_week, '2019' s_year, '2' s_month, '10' s_week from Dual union all select '20190301' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all select '20190302' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all select '20190303' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all select '20190304' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190305' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190306' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190307' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190308' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190309' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190310' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190311' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190312' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190313' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190314' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190315' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190316' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190317' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190318' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190319' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190320' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190321' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190322' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190323' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190324' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190325' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190326' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190327' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190328' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190329' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190330' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190331' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190401' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190402' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190403' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190404' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190405' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190406' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190407' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190408' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190409' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190410' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190411' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190412' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190413' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190414' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190415' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190416' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190417' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190418' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190419' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190420' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190421' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190422' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190423' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190424' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190425' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190426' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190427' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190428' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190429' d_date, '20190420' d_week, '2019' s_year, '4' s_month, '20' s_week from Dual union all select '20190430' d_date, '20190420' d_week, '2019' s_year, '4' s_month, '20' s_week from Dual union all select '20190501' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190502' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190503' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190504' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190505' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190506' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190507' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190508' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190509' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190510' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190511' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190512' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190513' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190514' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190515' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190516' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190517' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190518' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190519' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190520' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190521' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190522' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190523' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190524' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190525' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190526' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190527' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190528' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190529' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190530' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190531' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190601' d_date, '20190626' d_week, '2019' s_year, '6' s_month, '26' s_week from Dual union all select '20190602' d_date, '20190626' d_week, '2019' s_year, '6' s_month, '26' s_week from Dual union all select '20190603' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190604' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190605' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190606' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190607' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190608' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190609' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190610' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190611' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190612' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190613' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190614' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190615' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190616' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190617' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190618' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190619' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190620' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190621' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190622' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190623' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190624' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190625' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190626' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190627' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190628' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190629' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190630' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual )
by 마농
[2019.05.09 13:25:24]
SELECT a.code_n
, a.type
, a.no_p
, a.f_date
, a.t_date
, DECODE(gb, 'OLD', a.m_date, c.d_date) m_date
, a.m_week
, DECODE(d.lv, 1, a.gb, 'NORMAL') gb
, CASE WHEN a.gb = 'NORMAL' OR d.lv = 2 THEN a.rem_qty ELSE a.use_qty END use_qty
FROM (SELECT code_n
, type
, no_p
, f_date
, t_date
, m_date
, gb
, DECODE(gb, 'OLD', MAX(b_w), m_week) m_week
, SUM(use_qty) use_qty
, qty - SUM(SUM(use_qty)) OVER(PARTITION BY code_n, type, no_p, qty) rem_qty
, ROW_NUMBER() OVER(PARTITION BY code_n, type, no_p, qty ORDER BY m_week DESC) rn -- 요기
FROM (SELECT a.code_n, a.type, a.no_p, a.f_date, a.t_date, a.m_date
, CASE WHEN a.m_week < b.m_week THEN 'NEW'
WHEN a.m_week >= b.m_week THEN 'OLD'
ELSE 'NORMAL'
END gb
, b.m_week b_w
, CASE WHEN a.m_week < b.m_week THEN b.m_week ELSE a.m_week END m_week
, a.qty
, NVL(LEAST(a.s_qty - b.s_qty + b.qty, b.s_qty - a.s_qty + a.qty, a.qty, b.qty), 0) use_qty
FROM (SELECT code_n, type, no_p, f_date, t_date, m_date, m_week, qty
, SUM(qty) OVER(PARTITION BY code_n, type ORDER BY m_date, f_date, no_p) s_qty
FROM t
) a
LEFT OUTER JOIN
(SELECT code_n, type, m_week, qty
, SUM(qty) OVER(PARTITION BY code_n, type ORDER BY m_week) s_qty
FROM c
) b
ON a.code_n = b.code_n
AND a.type = b.type
AND a.s_qty > b.s_qty - b.qty
AND b.s_qty > a.s_qty - a.qty
)
GROUP BY code_n, type, f_date, t_date, no_p, gb, m_date, m_week, qty
) a
, (SELECT d_date, s_year, s_week
, ROW_NUMBER() OVER(PARTITION BY s_year, s_week ORDER BY d_date) rn
FROM calendar
) c
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) d
WHERE a.m_week LIKE c.s_year||'__'||c.s_week
AND c.rn = 1
AND d.lv <= CASE WHEN a.gb != 'NORMAL' AND a.rem_qty > 0 AND a.rn = 1 THEN 2 ELSE 1 END -- 요기
ORDER BY code_n, type, f_date, t_date, no_p, gb DESC
;
by 캘린다
[2019.05.09 14:49:25]
위와 같은 문제인줄알았는데, 원본 15값이 결과로 뽑힐때 13으로 뽑혀 수량차이가 2가 발생합니다. 이 하나를 제외한 나머지는 일치합니다 WITH t AS ( ---원본 select 'lg_sam' code_n, 'lemon' no_p, '20190114' f_date, '20190926' t_date, '20190926' m_date, '20190945' m_week, '36' qty, 'A' type From Dual union all select 'lg_sam' code_n, 'apple' no_p, '20181114' f_date, '20190425' t_date, '20190512' m_date, '20190522' m_week, '15' qty, 'A' type From Dual union all select 'lg_sam' code_n, 'apple' no_p, '20181114' f_date, '20190425' t_date, '20190521' m_date, '20190524' m_week, '15' qty, 'A' type From Dual union all select 'lg_sam' code_n, 'beer' no_p, '20190404' f_date, '20190605' t_date, '20190610' m_date, '20190628' m_week, '12' qty, 'S' type From Dual union all select 'lg_sam' code_n, 'boom' no_p, '20190322' f_date, '20190607' t_date, '20190610' m_date, '20190628' m_week, '150' qty, 'S' type From Dual union all select 'lg_sam' code_n, 'beer' no_p, '20190404' f_date, '20190605' t_date, '20190618' m_date, '20190629' m_week, '78' qty, 'S' type From Dual ) , c AS ( ---조건 select 'lg_sam' code_n, '20190417' m_week, '14' qty, 'A' type from Dual union all select 'lg_sam' code_n, '20190418' m_week, '14' qty, 'A' type from Dual union all select 'lg_sam' code_n, '20190417' m_week, '266' qty, 'S' type from Dual union all select 'lg_sam' code_n, '20190418' m_week, '266' qty, 'S' type from Dual union all select 'lg_sam' code_n, '20190522' m_week, '150' qty, 'S' type from Dual ) , calendar as ( select '20181101' d_date, '20181153' d_week, '2018' s_year, '11' s_month, '53' s_week from Dual union all select '20181102' d_date, '20181153' d_week, '2018' s_year, '11' s_month, '53' s_week from Dual union all select '20181103' d_date, '20181153' d_week, '2018' s_year, '11' s_month, '53' s_week from Dual union all select '20181104' d_date, '20181153' d_week, '2018' s_year, '11' s_month, '53' s_week from Dual union all select '20181105' d_date, '20181154' d_week, '2018' s_year, '11' s_month, '54' s_week from Dual union all select '20181106' d_date, '20181154' d_week, '2018' s_year, '11' s_month, '54' s_week from Dual union all select '20181107' d_date, '20181154' d_week, '2018' s_year, '11' s_month, '54' s_week from Dual union all select '20181108' d_date, '20181154' d_week, '2018' s_year, '11' s_month, '54' s_week from Dual union all select '20181109' d_date, '20181154' d_week, '2018' s_year, '11' s_month, '54' s_week from Dual union all select '20181110' d_date, '20181154' d_week, '2018' s_year, '11' s_month, '54' s_week from Dual union all select '20181111' d_date, '20181154' d_week, '2018' s_year, '11' s_month, '54' s_week from Dual union all select '20181112' d_date, '20181155' d_week, '2018' s_year, '11' s_month, '55' s_week from Dual union all select '20181113' d_date, '20181155' d_week, '2018' s_year, '11' s_month, '55' s_week from Dual union all select '20181114' d_date, '20181155' d_week, '2018' s_year, '11' s_month, '55' s_week from Dual union all select '20181115' d_date, '20181155' d_week, '2018' s_year, '11' s_month, '55' s_week from Dual union all select '20181116' d_date, '20181155' d_week, '2018' s_year, '11' s_month, '55' s_week from Dual union all select '20181117' d_date, '20181155' d_week, '2018' s_year, '11' s_month, '55' s_week from Dual union all select '20181118' d_date, '20181155' d_week, '2018' s_year, '11' s_month, '55' s_week from Dual union all select '20181119' d_date, '20181156' d_week, '2018' s_year, '11' s_month, '56' s_week from Dual union all select '20181120' d_date, '20181156' d_week, '2018' s_year, '11' s_month, '56' s_week from Dual union all select '20181121' d_date, '20181156' d_week, '2018' s_year, '11' s_month, '56' s_week from Dual union all select '20181122' d_date, '20181156' d_week, '2018' s_year, '11' s_month, '56' s_week from Dual union all select '20181123' d_date, '20181156' d_week, '2018' s_year, '11' s_month, '56' s_week from Dual union all select '20181124' d_date, '20181156' d_week, '2018' s_year, '11' s_month, '56' s_week from Dual union all select '20181125' d_date, '20181156' d_week, '2018' s_year, '11' s_month, '56' s_week from Dual union all select '20181126' d_date, '20181157' d_week, '2018' s_year, '11' s_month, '57' s_week from Dual union all select '20181127' d_date, '20181157' d_week, '2018' s_year, '11' s_month, '57' s_week from Dual union all select '20181128' d_date, '20181157' d_week, '2018' s_year, '11' s_month, '57' s_week from Dual union all select '20181129' d_date, '20181157' d_week, '2018' s_year, '11' s_month, '57' s_week from Dual union all select '20181130' d_date, '20181157' d_week, '2018' s_year, '11' s_month, '57' s_week from Dual union all select '20190101' d_date, '20190101' d_week, '2019' s_year, '1' s_month, '1' s_week from Dual union all select '20190102' d_date, '20190101' d_week, '2019' s_year, '1' s_month, '1' s_week from Dual union all select '20190103' d_date, '20190101' d_week, '2019' s_year, '1' s_month, '1' s_week from Dual union all select '20190104' d_date, '20190101' d_week, '2019' s_year, '1' s_month, '1' s_week from Dual union all select '20190105' d_date, '20190101' d_week, '2019' s_year, '1' s_month, '1' s_week from Dual union all select '20190106' d_date, '20190101' d_week, '2019' s_year, '1' s_month, '1' s_week from Dual union all select '20190107' d_date, '20190102' d_week, '2019' s_year, '1' s_month, '2' s_week from Dual union all select '20190108' d_date, '20190102' d_week, '2019' s_year, '1' s_month, '2' s_week from Dual union all select '20190109' d_date, '20190102' d_week, '2019' s_year, '1' s_month, '2' s_week from Dual union all select '20190110' d_date, '20190102' d_week, '2019' s_year, '1' s_month, '2' s_week from Dual union all select '20190111' d_date, '20190102' d_week, '2019' s_year, '1' s_month, '2' s_week from Dual union all select '20190112' d_date, '20190102' d_week, '2019' s_year, '1' s_month, '2' s_week from Dual union all select '20190113' d_date, '20190102' d_week, '2019' s_year, '1' s_month, '2' s_week from Dual union all select '20190114' d_date, '20190103' d_week, '2019' s_year, '1' s_month, '3' s_week from Dual union all select '20190115' d_date, '20190103' d_week, '2019' s_year, '1' s_month, '3' s_week from Dual union all select '20190116' d_date, '20190103' d_week, '2019' s_year, '1' s_month, '3' s_week from Dual union all select '20190117' d_date, '20190103' d_week, '2019' s_year, '1' s_month, '3' s_week from Dual union all select '20190118' d_date, '20190103' d_week, '2019' s_year, '1' s_month, '3' s_week from Dual union all select '20190119' d_date, '20190103' d_week, '2019' s_year, '1' s_month, '3' s_week from Dual union all select '20190120' d_date, '20190103' d_week, '2019' s_year, '1' s_month, '3' s_week from Dual union all select '20190121' d_date, '20190104' d_week, '2019' s_year, '1' s_month, '4' s_week from Dual union all select '20190122' d_date, '20190104' d_week, '2019' s_year, '1' s_month, '4' s_week from Dual union all select '20190123' d_date, '20190104' d_week, '2019' s_year, '1' s_month, '4' s_week from Dual union all select '20190124' d_date, '20190104' d_week, '2019' s_year, '1' s_month, '4' s_week from Dual union all select '20190125' d_date, '20190104' d_week, '2019' s_year, '1' s_month, '4' s_week from Dual union all select '20190126' d_date, '20190104' d_week, '2019' s_year, '1' s_month, '4' s_week from Dual union all select '20190127' d_date, '20190104' d_week, '2019' s_year, '1' s_month, '4' s_week from Dual union all select '20190128' d_date, '20190105' d_week, '2019' s_year, '1' s_month, '5' s_week from Dual union all select '20190129' d_date, '20190105' d_week, '2019' s_year, '1' s_month, '5' s_week from Dual union all select '20190130' d_date, '20190105' d_week, '2019' s_year, '1' s_month, '5' s_week from Dual union all select '20190131' d_date, '20190105' d_week, '2019' s_year, '1' s_month, '5' s_week from Dual union all select '20190301' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all select '20190302' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all select '20190303' d_date, '20190311' d_week, '2019' s_year, '3' s_month, '11' s_week from Dual union all select '20190304' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190305' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190306' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190307' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190308' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190309' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190310' d_date, '20190312' d_week, '2019' s_year, '3' s_month, '12' s_week from Dual union all select '20190311' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190312' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190313' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190314' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190315' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190316' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190317' d_date, '20190313' d_week, '2019' s_year, '3' s_month, '13' s_week from Dual union all select '20190318' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190319' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190320' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190321' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190322' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190323' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190324' d_date, '20190314' d_week, '2019' s_year, '3' s_month, '14' s_week from Dual union all select '20190325' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190326' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190327' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190328' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190329' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190330' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190331' d_date, '20190315' d_week, '2019' s_year, '3' s_month, '15' s_week from Dual union all select '20190401' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190402' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190403' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190404' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190405' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190406' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190407' d_date, '20190416' d_week, '2019' s_year, '4' s_month, '16' s_week from Dual union all select '20190408' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190409' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190410' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190411' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190412' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190413' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190414' d_date, '20190417' d_week, '2019' s_year, '4' s_month, '17' s_week from Dual union all select '20190415' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190416' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190417' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190418' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190419' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190420' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190421' d_date, '20190418' d_week, '2019' s_year, '4' s_month, '18' s_week from Dual union all select '20190422' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190423' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190424' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190425' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190426' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190427' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190428' d_date, '20190419' d_week, '2019' s_year, '4' s_month, '19' s_week from Dual union all select '20190429' d_date, '20190420' d_week, '2019' s_year, '4' s_month, '20' s_week from Dual union all select '20190430' d_date, '20190420' d_week, '2019' s_year, '4' s_month, '20' s_week from Dual union all select '20190501' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190502' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190503' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190504' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190505' d_date, '20190521' d_week, '2019' s_year, '5' s_month, '21' s_week from Dual union all select '20190506' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190507' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190508' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190509' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190510' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190511' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190512' d_date, '20190522' d_week, '2019' s_year, '5' s_month, '22' s_week from Dual union all select '20190513' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190514' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190515' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190516' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190517' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190518' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190519' d_date, '20190523' d_week, '2019' s_year, '5' s_month, '23' s_week from Dual union all select '20190520' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190521' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190522' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190523' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190524' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190525' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190526' d_date, '20190524' d_week, '2019' s_year, '5' s_month, '24' s_week from Dual union all select '20190527' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190528' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190529' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190530' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190531' d_date, '20190525' d_week, '2019' s_year, '5' s_month, '25' s_week from Dual union all select '20190601' d_date, '20190626' d_week, '2019' s_year, '6' s_month, '26' s_week from Dual union all select '20190602' d_date, '20190626' d_week, '2019' s_year, '6' s_month, '26' s_week from Dual union all select '20190603' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190604' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190605' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190606' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190607' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190608' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190609' d_date, '20190627' d_week, '2019' s_year, '6' s_month, '27' s_week from Dual union all select '20190610' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190611' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190612' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190613' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190614' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190615' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190616' d_date, '20190628' d_week, '2019' s_year, '6' s_month, '28' s_week from Dual union all select '20190617' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190618' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190619' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190620' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190621' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190622' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190623' d_date, '20190629' d_week, '2019' s_year, '6' s_month, '29' s_week from Dual union all select '20190624' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190625' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190626' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190627' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190628' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190629' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190630' d_date, '20190630' d_week, '2019' s_year, '6' s_month, '30' s_week from Dual union all select '20190901' d_date, '20190941' d_week, '2019' s_year, '9' s_month, '41' s_week from Dual union all select '20190902' d_date, '20190942' d_week, '2019' s_year, '9' s_month, '42' s_week from Dual union all select '20190903' d_date, '20190942' d_week, '2019' s_year, '9' s_month, '42' s_week from Dual union all select '20190904' d_date, '20190942' d_week, '2019' s_year, '9' s_month, '42' s_week from Dual union all select '20190905' d_date, '20190942' d_week, '2019' s_year, '9' s_month, '42' s_week from Dual union all select '20190906' d_date, '20190942' d_week, '2019' s_year, '9' s_month, '42' s_week from Dual union all select '20190907' d_date, '20190942' d_week, '2019' s_year, '9' s_month, '42' s_week from Dual union all select '20190908' d_date, '20190942' d_week, '2019' s_year, '9' s_month, '42' s_week from Dual union all select '20190909' d_date, '20190943' d_week, '2019' s_year, '9' s_month, '43' s_week from Dual union all select '20190910' d_date, '20190943' d_week, '2019' s_year, '9' s_month, '43' s_week from Dual union all select '20190911' d_date, '20190943' d_week, '2019' s_year, '9' s_month, '43' s_week from Dual union all select '20190912' d_date, '20190943' d_week, '2019' s_year, '9' s_month, '43' s_week from Dual union all select '20190913' d_date, '20190943' d_week, '2019' s_year, '9' s_month, '43' s_week from Dual union all select '20190914' d_date, '20190943' d_week, '2019' s_year, '9' s_month, '43' s_week from Dual union all select '20190915' d_date, '20190943' d_week, '2019' s_year, '9' s_month, '43' s_week from Dual union all select '20190916' d_date, '20190944' d_week, '2019' s_year, '9' s_month, '44' s_week from Dual union all select '20190917' d_date, '20190944' d_week, '2019' s_year, '9' s_month, '44' s_week from Dual union all select '20190918' d_date, '20190944' d_week, '2019' s_year, '9' s_month, '44' s_week from Dual union all select '20190919' d_date, '20190944' d_week, '2019' s_year, '9' s_month, '44' s_week from Dual union all select '20190920' d_date, '20190944' d_week, '2019' s_year, '9' s_month, '44' s_week from Dual union all select '20190921' d_date, '20190944' d_week, '2019' s_year, '9' s_month, '44' s_week from Dual union all select '20190922' d_date, '20190944' d_week, '2019' s_year, '9' s_month, '44' s_week from Dual union all select '20190923' d_date, '20190945' d_week, '2019' s_year, '9' s_month, '45' s_week from Dual union all select '20190924' d_date, '20190945' d_week, '2019' s_year, '9' s_month, '45' s_week from Dual union all select '20190925' d_date, '20190945' d_week, '2019' s_year, '9' s_month, '45' s_week from Dual union all select '20190926' d_date, '20190945' d_week, '2019' s_year, '9' s_month, '45' s_week from Dual union all select '20190927' d_date, '20190945' d_week, '2019' s_year, '9' s_month, '45' s_week from Dual union all select '20190928' d_date, '20190945' d_week, '2019' s_year, '9' s_month, '45' s_week from Dual union all select '20190929' d_date, '20190945' d_week, '2019' s_year, '9' s_month, '45' s_week from Dual union all select '20190930' d_date, '20190946' d_week, '2019' s_year, '9' s_month, '46' s_week from Dual )
by 마농
[2019.05.09 14:56:30]
원인과 해결책은 이미 알려드렸는데요.
일단 qty 를 추가했지만, 더 추가해야 할지도 모른다고 이미 답변 드렸습니다.
no_p 다음 qty 까지도 중복되네요.
- 수정전 : , qty - SUM(SUM(use_qty)) OVER(PARTITION BY code_n, type, no_p, qty) rem_qty
- 수정후 : , qty - SUM(SUM(use_qty)) OVER(PARTITION BY code_n, type, no_p, f_date, t_date, m_date, qty) rem_qty
by 캘린다
[2019.05.09 15:22:06]
감사합니다 m_week가 달라서 중복이 아닌줄 알았는데, 좀 더 공부를 해야겠네요
) 버튼을 클릭하여 작성 하시면 됩니다.