오라클 sq l 계산 쿼리 질문입니다. 0 30 286

by 캘린다 [SQL Query] [2019.03.15 08:31:27]


SELECT 'LG_SAM' CODE_N ,   '521102-01' NO_P,   '20181228' F_Date, '20190101'  M_Date,   '20190101' M_Week ,  50 QTY FROM DUAL UNION
SELECT 'LG_SAM'  CODE_N ,  '521102-02' NO_P,   '20181115' F_Date,  '20190108'  M_Date,   '20190102'  M_Week,  100 QTY FROM DUAL UNION
SELECT  'LG_SAM' CODE_N ,   '521102-03'  NO_P,  '20181115'  F_Date,  '20190108' M_Date,    '20190102' M_Week ,  100 QTY FROM DUAL UNION
SELECT  'LG_SAM' CODE_N ,   '521102-03'  NO_P,  '20181115'  F_Date,  '20190114' M_Date,    '20190103' M_Week ,  200 QTY FROM DUAL UNION
SELECT 'LG_SAM' CODE_N  , '521102-04'  NO_P, '20181120'  F_Date,  '20190121'  M_Date,   '20190104'  M_Week,  100 QTY  FROM DUAL UNION
SELECT 'LG_SAM' CODE_N  , '521102-04'  NO_P, '20181115'  F_Date,  '20190122'  M_Date,   '20190104'  M_Week,  100 QTY  FROM DUAL 
CODE_N NO_P F_DATE M_DATE M_WEEK QTY
LG_SAM 521102-01 20181228 20190101 20190101 50
LG_SAM 521102-02 20181115 20190108 20190102 100
LG_SAM 521102-03 20181115 20190108 20190102 100
LG_SAM 521102-03 20181115 20190114 20190103 200
LG_SAM 521102-04 20181115 20190122 20190104 100
LG_SAM 521102-04 20181120 20190121 20190104 100

 

select 'LG-SAM' CODE_N, '100' week1, '150' week2, '150' week3, '250' week4 from dual

CODE_N WEEK1 WEEK2 WEEK3 WEEK4
LG-SAM 100 150 150 250

위는 예습 DATA 입니다.

원본 데이터

1주차 SUM = 50 2주차 SUM = 200 3주차 SUM = 200 4주차 SUM = 200

조건 DATA는 1주차 100 , 2주차 150, 3주차 150, 4주차 250입니다.

조건 DATA 기준으로 주차별 원본 Data가 1주차 기준으로 100을 초과하면 안되고 , 반대로 위와 같이 50이 남는경우 2주차로 누적되어

원본 DATA 200 = 조건 DATA 2주차+1주차 누적 200 으로 출력이 되어야합니다.

원본에서 3주차 200이 조건 150을 초과한경우 남는 50은 원본 Data 4주차에 포함시켜야 하는데 이때 50이 더해저야 하는곳은 F_DATA 날짜가 빠른곳입니다.

 

GREATEST 함수하고, over(partition by 함수를 이용해서 하려고하는데...결과값이 제대로 나오지않아 도움을 받고자 질문을 올립니다.

결과값

CODEN NO_P F_DATE M_DATE M_WEEK QTY
LG_SAM 521102-01 20181228 20190101 20190101 50
LG_SAM 521102-02 20181115 20190108 20190102 100
LG_SAM 521102-03 20181115 20190108 20190102 100
LG_SAM 521102-03 20181115 20190114 20190103 150
LG_SAM 521102-04 20181115 20190122 20190104 100
LG_SAM 521102-04 20181120 20190121 20190104 150

 

by 마농 [2019.03.15 10:54:56]
WITH t AS
(
SELECT 'LG_SAM' code_n, '521102-01' no_p, '20181228' f_date, '20190101' m_date, '20190101' m_week, 50 qty FROM dual
UNION ALL SELECT 'LG_SAM', '521102-02', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190114', '20190103', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181115', '20190122', '20190104', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181120', '20190121', '20190104', 100 FROM dual
)
, c AS
(
SELECT 'LG_SAM' code_n, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'LG_SAM', '20190102', 150 FROM dual
UNION ALL SELECT 'LG_SAM', '20190103', 150 FROM dual
UNION ALL SELECT 'LG_SAM', '20190104', 250 FROM dual
)
SELECT code_n, no_p, f_date, m_date, m_week, qty
     , SUM(v) + NVL(SUM(DECODE(rn, 1, x)), 0) x
  FROM (SELECT a.code_n, a.no_p, a.f_date, a.m_date
             , GREATEST(a.m_week, b.m_week) m_week
             , a.qty
             , LEAST( a.s_qty - b.s_qty + b.qty
                    , b.s_qty - a.s_qty + a.qty
                    , a.qty
                    , b.qty
                    ) v
             , SUM(CASE WHEN a.m_week < b.m_week THEN
               LEAST( a.s_qty - b.s_qty + b.qty
                    , b.s_qty - a.s_qty + a.qty
                    , a.qty
                    , b.qty
                    )
                END) OVER(PARTITION BY b.m_week) x
             , ROW_NUMBER() OVER(PARTITION BY b.m_week
               ORDER BY a.m_week DESC, a.no_p DESC, a.f_date DESC, a.m_date DESC) rn
             , CASE WHEN a.m_week < b.m_week THEN 0 ELSE 1 END flag
          FROM (SELECT code_n, no_p, f_date, m_date, m_week, qty
                     , SUM(qty) OVER(ORDER BY m_week, no_p, f_date, m_date) s_qty
                  FROM t
                 WHERE code_n = 'LG_SAM'
                   AND m_week LIKE '201901%'
                ) a
             , (SELECT code_n, m_week, qty
                     , SUM(qty) OVER(ORDER BY m_week) s_qty
                  FROM c
                 WHERE code_n = 'LG_SAM'
                   AND m_week LIKE '201901%'
                ) b
         WHERE a.s_qty > b.s_qty(+) - b.qty(+)
           AND b.s_qty(+) > a.s_qty - a.qty
        )
 WHERE flag = 1
 GROUP BY code_n, no_p, f_date, m_date, m_week, qty
 ORDER BY code_n, m_week, no_p, f_date, m_date
;

 


by 캘린다 [2019.03.15 12:11:31]

와...감사합니다.

한 가지 더 궁금한게 있습니다.

지금 작성해주신 SQL은

조건 data로 인하여 초과가 되는 주차는 f_date가 가장 큰 곳에 더해주게되는데

조건data로 인해 초과가 되는 주차는 다음주차에 f_date가 가장큰곳에 더 해주고 (F_date가 같을때는 M_date가 해당 주차에 가장 낮은곳), 그다음 주차가 조건data에 의해 또 초과되었을시 그 다음주차 f_date가 큰곳으로 더해 줄 수 있나요?

초과된 다음주가 f_date가 같으면 m_date가 빠른곳 


by 마농 [2019.03.15 15:06:31]

예를 들어 주세요.
자료 예시.
원본 대비 결과표.


by 캘린다 [2019.03.17 08:09:55]

원본 DATA

CODEN NO_P F_DATE M_DATE M_WEEK QTY
LG_SAM 521102-01 20181228 20190101 20190101 150
LG_SAM 521102-02 20181115 20190108 20190102 100
LG_SAM 521102-03 20181115 20190107 20190102 50
LG_SAM 521102-03 20181115 20190114 20190103 200
LG_SAM 521102-04 20181115 20190122 20190104 100
LG_SAM 521102-04 20181120 20190121 20190104 150

조건 DATA

CODE_N WEEK1 WEEK2 WEEK3 WEEK4
LG-SAM 100 200 250 200

결과값

CODEN NO_P F_DATE M_DATE M_WEEK QTY
LG_SAM 521102-01 20181228 20190101 20190101 100
LG_SAM 521102-02 20181115 20190108 20190102 100
LG_SAM 521102-03 20181115 20190107 20190102 100
LG_SAM 521102-03 20181115 20190114 20190103 200
LG_SAM 521102-04 20181115 20190122 20190104 100
LG_SAM 521102-04 20181120 20190121 20190104 150

 

입니다. 위 본문의 내용에 댓글 질문을 추가로 한 예시입니다.

1주차 조건 100 원본 150

원본에서 50이 남음으로 2주차로 넘어감 하지만, 2주차 F_date가 같으므로 M_date가 작은 190107에 50을 더해줌 

3주차 조건 250 원본 200 조건에서 50이남음, 남은 50은 4주차 누적되어 원본 250을 나오게함

 

 


by 마농 [2019.03.18 11:04:24]
WITH t AS
(
SELECT 'lg_sam' code_n, '521102-01' no_p, '20181228' f_date, '20190101' m_date, '20190101' m_week, 50 qty FROM dual
UNION ALL SELECT 'lg_sam', '521102-02', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-03', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-03', '20181115', '20190114', '20190103', 200 FROM dual
UNION ALL SELECT 'lg_sam', '521102-04', '20181115', '20190122', '20190104', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-04', '20181120', '20190121', '20190104', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-01', '20181228', '20190101', '20190101', 150 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-02', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190107', '20190102',  50 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190114', '20190103', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181115', '20190122', '20190104', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181120', '20190121', '20190104', 150 FROM dual
)
, c AS
(
SELECT 'lg_sam' code_n, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 150 FROM dual
UNION ALL SELECT 'lg_sam', '20190103', 150 FROM dual
UNION ALL SELECT 'lg_sam', '20190104', 250 FROM dual
UNION ALL SELECT 'LG_SAM', '20190101', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '20190102', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '20190103', 250 FROM dual
UNION ALL SELECT 'LG_SAM', '20190104', 200 FROM dual
)
SELECT code_n, no_p, f_date, m_date, m_week, qty
     , SUM(v) + NVL(SUM(DECODE(rn, 2, x)), 0) x
  FROM (SELECT a.code_n, a.no_p, a.f_date, a.m_date
             , GREATEST(a.m_week, b.m_week) m_week
             , a.m_week aw, b.m_week bw
             , a.qty
             , LEAST( a.s_qty - b.s_qty + b.qty
                    , b.s_qty - a.s_qty + a.qty
                    , a.qty
                    , b.qty
                    ) v
             , SUM(CASE WHEN a.m_week < b.m_week THEN
               LEAST( a.s_qty - b.s_qty + b.qty
                    , b.s_qty - a.s_qty + a.qty
                    , a.qty
                    , b.qty
                    )
                END) OVER(PARTITION BY a.code_n, b.m_week) x
             , ROW_NUMBER() OVER(PARTITION BY a.code_n, b.m_week
               ORDER BY a.m_week, a.f_date, a.m_date, a.no_p) rn
             , CASE WHEN a.m_week < b.m_week THEN 0 ELSE 1 END flag
          FROM (SELECT code_n, no_p, f_date, m_date, m_week, qty
                     , SUM(qty) OVER(PARTITION BY code_n
                       ORDER BY m_week, f_date, m_date, no_p) s_qty
                  FROM t
                 WHERE m_week LIKE '201901%'
                ) a
             , (SELECT code_n, m_week, qty
                     , SUM(qty) OVER(PARTITION BY code_n ORDER BY m_week) s_qty
                  FROM c
                 WHERE m_week LIKE '201901%'
                ) b
         WHERE a.code_n = b.code_n
           AND a.s_qty > b.s_qty - b.qty
           AND b.s_qty > a.s_qty - a.qty
        )
 WHERE flag = 1
 GROUP BY code_n, no_p, f_date, m_date, m_week, qty
 ORDER BY code_n, no_p, m_week, f_date, m_date
;

 


by 캘린다 [2019.03.18 11:16:42]
WITH t AS
(
SELECT 'lg_sam' code_n, '521102-01' no_p, '20181228' f_date, '20190101' m_date, '20190101' m_week, 50 qty FROM dual
UNION ALL SELECT 'lg_sam', '521102-02', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-03', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-03', '20181115', '20190114', '20190103', 200 FROM dual
UNION ALL SELECT 'lg_sam', '521102-04', '20181115', '20190122', '20190104', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-04', '20181120', '20190121', '20190104', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-01', '20181228', '20190401', '20190414', 150 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-02', '20181115', '20190408', '20190415', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190407', '20190414',  50 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190414', '20190415', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181115', '20190422', '20190417', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181120', '20190421', '20190416', 150 FROM dual
)
, c AS
(
SELECT 'lg_sam' code_n, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 150 FROM dual
UNION ALL SELECT 'lg_sam', '20190103', 150 FROM dual
UNION ALL SELECT 'lg_sam', '20190104', 250 FROM dual
UNION ALL SELECT 'LG_SAM', '20190414', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '20190415', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '20190416', 250 FROM dual
UNION ALL SELECT 'LG_SAM', '20190417', 200 FROM dual
)
 WHERE flag = 1

--AND SUBSTR(a.m_week, 1, 6) = SUBSTR(b.m_week, 1, 6)

 

궁금한점이있는데, 하기 where에 m_week는 조건을 주셧는데

만약 다른 주차들도 여러개가 존재한다면

그래서 해당 where삭제를 하엿는데 data가 꼬이거나 x값이 제대로 출력이 안됩니다.

 

a 부분 쿼리에 partition by에 m_date를 넣엇더니 제대로 나오는것 같긴한데. 맞는지 제대로 한건지 모르겠네요


by 마농 [2019.03.18 13:24:55]
-- 조건 추가해 보세요.
AND SUBSTR(a.m_week, 1, 6) = SUBSTR(b.m_week, 1, 6)

 


by 캘린다 [2019.03.18 14:04:44]
WITH t AS
(
SELECT 'lg_sam' code_n, '521102-01' no_p, '20181228' f_date, '20190101' m_date, '20190101' m_week, 50 qty FROM dual
UNION ALL SELECT 'lg_sam', '521102-02', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-03', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-03', '20181115', '20190114', '20190103', 200 FROM dual
UNION ALL SELECT 'lg_sam', '521102-04', '20181115', '20190122', '20190104', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521102-04', '20181120', '20190121', '20190104', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-01', '20181228', '20190401', '20190414', 150 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-02', '20181115', '20190408', '20190415', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190407', '20190414',  50 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190414', '20190415', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181115', '20190422', '20190417', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181120', '20190421', '20190416', 150 FROM dual
)
, c AS
(
SELECT 'lg_sam' code_n, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 150 FROM dual
UNION ALL SELECT 'lg_sam', '20190103', 150 FROM dual
UNION ALL SELECT 'lg_sam', '20190104', 250 FROM dual
UNION ALL SELECT 'LG_SAM', '20190414', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '20190415', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '20190416', 250 FROM dual
UNION ALL SELECT 'LG_SAM', '20190417', 200 FROM dual
)

--------조건 및 원본 DATA 주차 다르게 수정

;

내용 형식 DATA를 201901 에서 LG_SAM만 201904월~ 주차로 표기하였더니

row수 12개에서 > 11개로 줄고

초과되는 다음차수의 f_date큰값에 들어가지지가 않습니다

조건값에 m_date가 없어서 못찾아주는걸까요..ㅠㅠ


by 마농 [2019.03.18 17:58:57]

f_date, m_date, m_week, no_p 의 정렬 기준이 모호합니다.
예시 자료를 주실 때 마다 정렬이 달라지는 것 같네요.
이 네가지 항목이 정비례 하면서 올라가야 할 것 같은데...뒤죽박죽 이네요.


by 캘린다 [2019.03.19 08:38:17]

1.원본값은 조건값을 초과할 수 없다.

2.원본값200 조건값 150이면 원본값 나머지 50은 다음 주차로 누적됨 / 원본값150 조건값200이면 조건값 나머지 50이 다음 주차로 누적됨

3. 누적될시 해당 주차에 F_date가 큰곳에 누적됨

4. F_Date가 같은경우 M_date가 Max인곳에 누적

원문내용과 위와 기준은 같습니다.

 

원본값

CODE_N

NO_P F_DATE M_DATE M_WEEK QTY
lg_sam 521101-01 20181228 20190101 20190101 50
lg_sam 521101-02 20181115 20190108 20190102 100
lg_sam 521101-03 20181115 20190108 20190102 100
lg_sam 521101-04 20181115 20190114 20190103 200
lg_sam 521101-05 20181115 20190122 20190104 100
lg_sam 521101-06 20181120 20190121 20190104 100
LG_SAM 521102-01 20181228 20190401 20190414 150
LG_SAM 521102-02 20181115 20190408 20190415 100
LG_SAM 521102-03 20181115 20190407 20190414 50
LG_SAM 521102-03 20181115 20190414 20190415 200
LG_SAM 521102-04 20181115 20190422 20190417 100
LG_SAM 521102-04 20181120 20190421 20190416 150
BOY 521103-01 20190105 20190801 20190831 100
BOY 521103-02 20190106 20190804 20190831 100
BOY 521103-03 20190107 20190815 20190833 100
BOY 521103-04 20190108 20190823 20190834 100
BOY 521103-05 20190109 20190824 20190834 100
BOY 521103-06 20190110 20190825 20190834 100
BOY 521103-07 20190111 20190830 20190835 100
BOY 521103-08 20190112 20190831 20190835 100

 조건값

CODE_N M_WEEK QTY
lg_sam 20190101 100
lg_sam 20190102 150
lg_sam 20190103 150
lg_sam 20190104 250
LG_SAM 20190414 100
LG_SAM 20190415 200
LG_SAM 20190416 250
LG_SAM 20190417 200
BOY 20190831 150
BOY 20190833 200
BOY 20190834 250
BOY 20190835 200

결과값

CODE_N NO_P F_DATE M_DATE M_WEEK QTY 결과값 조건값
lg_sam 521101-01 20181228 20190101 20190101 50 50 100
lg_sam 521101-02 20181115 20190108 20190102 100 100  
lg_sam 521101-03 20181115 20190108 20190102 100 100 150
lg_sam 521101-04 20181115 20190114 20190103 200 150 150
lg_sam 521101-05 20181115 20190122 20190104 100 150  
lg_sam 521101-06 20181120 20190121 20190104 100 100 250
LG_SAM 521102-01 20181228 20190401 20190414 150 100  
LG_SAM 521102-03 20181115 20190407 20190414 50 0 100
LG_SAM 521102-02 20181115 20190408 20190415 100 100  
LG_SAM 521102-03 20181115 20190414 20190415 200 100 200
LG_SAM 521102-04 20181120 20190421 20190416 150 250 250
LG_SAM 521102-04 20181115 20190422 20190417 100 200 200
BOY 521103-01 20190105 20190801 20190831 100 50  
BOY 521103-02 20190106 20190804 20190831 100 100 150
BOY 521103-03 20190107 20190815 20190833 100 150 200
BOY 521103-04 20190108 20190823 20190834 100 100  
BOY 521103-05 20190109 20190824 20190834 100 100  
BOY 521103-06 20190110 20190825 20190834 100 100 250
BOY 521103-07 20190111 20190830 20190835 100 100  
BOY 521103-08 20190112 20190831 20190835 100 100

200

 뒤죽박죽이여서 다시 정리하였습니다.

 

SELECT 'lg_sam' code_n, '521101-01' no_p, '20181228' f_date, '20190101' m_date, '20190101' m_week, 50 qty FROM dual
UNION ALL SELECT 'lg_sam', '521101-02', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-03', '20181115', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-04', '20181115', '20190114', '20190103', 200 FROM dual
UNION ALL SELECT 'lg_sam', '521101-05', '20181115', '20190122', '20190104', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-06', '20181120', '20190121', '20190104', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-01', '20181228', '20190401', '20190414', 150 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-03', '20181115', '20190407', '20190414',  50 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-02', '20181115', '20190408', '20190415', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-04', '20181115', '20190414', '20190415', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-06', '20181120', '20190421', '20190416', 150 FROM dual
UNION ALL SELECT 'LG_SAM', '521102-05', '20181115', '20190422', '20190417', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521103-01', '20190105', '20190801', '20190831', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521103-02', '20190106', '20190804', '20190831', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521103-03', '20190107', '20190815', '20190833', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521103-04', '20190108', '20190823', '20190834', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521103-05', '20190109', '20190824', '20190834', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521103-06', '20190110', '20190825', '20190834', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521103-07', '20190111', '20190830', '20190835', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '521103-08', '20190112', '20190831', '20190835', 100 FROM dual


SELECT 'lg_sam' code_n, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 150 FROM dual
UNION ALL SELECT 'lg_sam', '20190103', 150 FROM dual
UNION ALL SELECT 'lg_sam', '20190104', 250 FROM dual
UNION ALL SELECT 'LG_SAM', '20190414', 100 FROM dual
UNION ALL SELECT 'LG_SAM', '20190415', 200 FROM dual
UNION ALL SELECT 'LG_SAM', '20190416', 250 FROM dual
UNION ALL SELECT 'LG_SAM', '20190417', 200 FROM dual
UNION ALL SELECT 'BOY', '20190831', 150 FROM dual
UNION ALL SELECT 'BOY', '20190833', 200 FROM dual
UNION ALL SELECT 'BOY', '20190834', 250 FROM dual
UNION ALL SELECT 'BOY', '20190835', 200 FROM dual

 


by 캘린다 [2019.03.19 08:54:57]

ddd


by 마농 [2019.03.19 09:46:37]

정렬이 모호합니다.
no_p, f_date, m_date, m_week 이 4가지가
서로 증가되는 양상이 다르네요.
이중 어느것이 정렬기준이 되어야 할지 모호한 상황입니다.
순차적인 차감로직이 수행되려면 명확한 정렬기준이 있어야 합니다.
그런데 어느 항목은 증가가 되는데 어느 항목은 반대로 감소가 되는 등.
정렬기준을 어떻게 가져가야 할지 모르겠네요.


by 캘린다 [2019.03.19 09:49:35]

정렬기준을 code_n으로 잡아도 문제가되는것이지요?

4번을 빼도 문제가될까요?

만약 마농님이시라면 어떻게 하는게 좋으실지....


by 마농 [2019.03.19 10:35:24]

순서가 꼬이고 있습니다. 차감 되는 순서대로 정렬이 되어야 합니다.
기준이 모호하면 방법이 없습니다. 기준이 명확해야 합니다.
원래 자료가 이런건지? 예시자료를 잘못 만들어 주신건 아닌지?


by 캘린다 [2019.03.19 16:59:45]

예시자료 다시 작성하였습니다.
1. F_DATE 가 빠른 데이타 부터 조건 데이타를 사용하고 조건데이타가 남는경우 다음주차 조건데이타에 누적

2. 원본데이타가 초과된경우 초과된 수량만 NO_P의 M_date,M_week의 날짜와 주차를 해당하는 다음주차의 날짜(MAX), 주차로 변경(Row 수가 1개증가) , 조건Data의 변경된 주차의 M_WEEK 고려해야함

예) NO_P = 521101-02 가 50 초과된경우 날짜와,주차가 바뀐 데이타로 row가 늘어나야함

3. 조건데이타가 F_DATE 빠른순으로 채워주면서 원본데이타가 계속 초과된 경우

   = 2번과 같이 계속 늘려줘야함 (다음주차로)


(달력 데이타는 code로 첨부)

원본데이타

CODE_N NO_P F_DATE M_DATE M_WEEK QTY
lg_sam 521101-01 20180101 20190101 20190101 100
lg_sam 521101-02 20180102 20190102 20190101 100
lg_sam 521101-03 20180103 20190107 20190102 100
lg_sam 521101-04 20180104 20190108 20190102 100
LG-SAM 521102-01 20180105 20190107 20190102 100
LG-SAM 521102-02 20180106 20190108 20190102 100
LG-SAM 521102-03 20180107 20190109 20190102 100
LG-SAM 521102-04 20180108 20190114 20190103 100
결과 데이타
CODE_N NO_P F_DATE M_DATE M_WEEK QTY
lg_sam 521101-01 20180101 20190101 20190101 100
lg_sam 521101-02 20180102 20190102 20190101 50
lg_sam 521101-03 20180103 20190107 20190102 100
lg_sam 521101-04 20180104 20190108 20190102 100
lg_sam 521101-02 20180102 2.02E+08 20190102 50
LG-SAM 521102-01 20180105 20190107 20190102 100
LG-SAM 521102-02 20180106 20190108 20190102 100
LG-SAM 521102-03 20180107 20190109 20190102 100
LG-SAM 521102-04 20180108 20190114 20190103 100



--달력
     SELECT   '20190101' d_date, '20190101' d_week from dual UNION ALL
     SELECT   '20190102' d_date, '20190101' d_week FROM DUAL UNION ALL
     SELECT   '20190103' d_date, '20190101' d_week FROM DUAL UNION ALL
     SELECT   '20190104' d_date, '20190101' d_week FROM DUAL UNION ALL
     SELECT   '20190105' d_date, '20190101' d_week FROM DUAL UNION ALL
     SELECT   '20190106' d_date, '20190101' d_week FROM DUAL UNION ALL
     SELECT   '20190107' d_date, '20190102' d_week FROM DUAL UNION ALL
     SELECT   '20190108' d_date, '20190102' d_week   FROM DUAL UNION ALL
     SELECT   '20190109' d_date, '20190102' d_week   FROM DUAL UNION ALL
     SELECT   '20190110' d_date, '20190102' d_week   FROM DUAL UNION ALL
     SELECT   '20190111' d_date, '20190102' d_week   FROM DUAL UNION ALL
     SELECT   '20190112' d_date, '20190102' d_week   FROM DUAL UNION ALL
     SELECT   '20190113' d_date, '20190102' d_week   FROM DUAL UNION ALL
     SELECT   '20190114' d_date, '20190103' d_week   FROM DUAL UNION ALL
     SELECT   '20190115' d_date, '20190103' d_week   FROM DUAL UNION ALL
     SELECT   '20190116' d_date, '20190103' d_week   FROM DUAL UNION ALL
     SELECT   '20190117' d_date, '20190103' d_week   FROM DUAL UNION ALL
     SELECT   '20190118' d_date, '20190103' d_week   FROM DUAL UNION ALL
     SELECT   '20190119' d_date, '20190103' d_week   FROM DUAL UNION ALL
     SELECT   '20190120' d_date, '20190103' d_week   FROM DUAL UNION ALL
     SELECT   '20190121' d_date, '20190104' d_week   FROM DUAL UNION ALL
     SELECT   '20190122' d_date, '20190104' d_week   FROM DUAL UNION ALL
     SELECT   '20190123' d_date, '20190104' d_week   FROM DUAL UNION ALL
     SELECT   '20190124' d_date, '20190104' d_week   FROM DUAL UNION ALL
     SELECT   '20190125' d_date, '20190104' d_week   FROM DUAL UNION ALL
     SELECT   '20190126' d_date, '20190104' d_week   FROM DUAL UNION ALL
     SELECT   '20190127' d_date, '20190104' d_week   FROM DUAL UNION ALL
     SELECT   '20190128' d_date, '20190105' d_week   FROM DUAL UNION ALL
     SELECT   '20190129' d_date, '20190105' d_week   FROM DUAL UNION ALL
     SELECT   '20190130' d_date, '20190105' d_week   FROM DUAL UNION ALL
     SELECT   '20190131' d_date, '20190105' d_week   FROM DUAL UNION

--원본
SELECT 'lg_sam' code_n, '521101-01' no_p, '20180101' f_date, '20190101' m_date, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'lg_sam', '521101-02', '20180102', '20190102', '20190101', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-03', '20180103', '20190107', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-04', '20180104', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-01', '20180105', '20190107', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-02', '20180106', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-03', '20180107', '20190109', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-04', '20180108', '20190114', '20190103', 100 FROM dual

--조건
SELECT 'lg_sam' code_n, '20190101' m_week, 150 qty FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 250 FROM dual
UNION ALL SELECT 'LG-SAM', '20190102',350 FROM dual
UNION ALL SELECT 'LG-SAM', '20190103', 50 FROM dual

 


by 마농 [2019.03.19 17:27:28]

결과표의 lg_sam 이 4줄에서 5줄로 늘었는데.
초과되어 이관된 50 이 합쳐지지 않고 별도행으로 표현하면 되는 건가요?
이렇게 되면 더 간단해 질 것 같네요.
억지로 합치느라 힘들었는데요.
 

WITH t AS
(
SELECT 'lg_sam' code_n, '521101-01' no_p, '20180101' f_date, '20190101' m_date, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'lg_sam', '521101-02', '20180102', '20190102', '20190101', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-03', '20180103', '20190107', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-04', '20180104', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-01', '20180105', '20190107', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-02', '20180106', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-03', '20180107', '20190109', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-04', '20180108', '20190114', '20190103', 100 FROM dual
)
, c AS
(
SELECT 'lg_sam' code_n, '20190101' m_week, 150 qty FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 250 FROM dual
UNION ALL SELECT 'LG-SAM', '20190102', 350 FROM dual
UNION ALL SELECT 'LG-SAM', '20190103',  50 FROM dual
)
SELECT a.code_n, a.no_p, a.f_date, a.m_date
     , a.m_week a_week
     , b.m_week b_week
     , a.qty    a_qty
     , b.qty    b_qty
     , LEAST( a.s_qty - b.s_qty + b.qty
            , b.s_qty - a.s_qty + a.qty
            , a.qty
            , b.qty
            ) use_qty
  FROM (SELECT code_n, no_p, f_date, m_date, m_week, qty
             , SUM(qty) OVER(PARTITION BY code_n ORDER BY no_p) s_qty
          FROM t
        ) a
     , (SELECT code_n, m_week, qty
             , SUM(qty) OVER(PARTITION BY code_n ORDER BY m_week) s_qty
          FROM c
        ) b
 WHERE a.code_n = b.code_n
   AND a.s_qty > b.s_qty - b.qty
   AND b.s_qty > a.s_qty - a.qty
 ORDER BY code_n, no_p, f_date, a_week, b_week
;

 


by 캘린다 [2019.03.19 20:30:21]

넵 맞습니다 별도로 표현되야합니다!

와 감사합니다

그런데 초과되면서 새로 생성되는 Row의 M_date는 바뀌는 다음 주차의 MAX값 date로는 불가능할까요?

주차는 변경이되는데 M_date값을 따로 달력쿼리랑 물려서 max값으로 줘야하는지...


by 마농 [2019.03.20 08:08:59]

원하시는 결과표를 보여주세요.


by 캘린다 [2019.03.20 08:18:50]
gubun

CODE_N

NO_P F_DATE M_DATE M_WEEK QTY
OLD

lg_sam

521101-01 20180101 20190101 20190101 100
OLD lg_sam 521101-02 20180102 20190102 20190101 50
OLD lg_sam 521101-03 20180103 20190107 20190102 100
OLD lg_sam 521101-04 20180104 20190108 20190102 100
NEW lg_sam 521101-02 20180102 20190111 20190102 50
OLD LG-SAM 521102-01 20180105 20190107 20190102 100
OLD LG-SAM 521102-02 20180106 20190108 20190102 100
OLD LG-SAM 521102-03 20180107 20190109 20190102 100
OLD LG-SAM 521102-04 20180108 20190114 20190103 100

 

어제와 같은 결과표에 추가하였습니다.. 어제 결과표를 확인해보니

lg_sam 의 새로 생성된 Row의 m_date가 이상하게 적혀있어서 다시 수정하고, 추가로 gubun자를 보았습니다.

새로운 Row가 생기면수 기존qty와 새로운 use_qty의 구분이 어려워(기존qty의 수량이 증가로)

새로 추가된 로우에 대해선 NEW표기가 가능한가요?

만들어주신 쿼리가 위의 예시한정인지.좀 더 code_n과 no_P가 많아져도 괜찮을까요?

아 gubun은 a_week하고 b_week하고 -햇을때 차이가 있으면 NEW로 나오게 하였습니다.

WITH t AS
(
SELECT 'lg_sam' code_n, '521101-01' no_p, '20180101' f_date, '20190101' m_date, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'lg_sam', '521101-02', '20180102', '20190102', '20190101', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-03', '20180103', '20190107', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-04', '20180104', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-05', '20180105', '20190108', '20190102', 50 FROM dual
UNION ALL SELECT 'lg_sam', '521101-04', '20180104', '20190114', '20190103', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-01', '20180105', '20190107', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-02', '20180106', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-03', '20180107', '20190109', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-04', '20180108', '20190114', '20190103', 100 FROM dual
)
, c AS
(
SELECT 'lg_sam' code_n, '20190101' m_week, 150 qty FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 250 FROM dual
UNION ALL SELECT 'lg_sam', '20190103', 150 FROM dual
UNION ALL SELECT 'LG-SAM', '20190102', 350 FROM dual
UNION ALL SELECT 'LG-SAM', '20190103',  50 FROM dual
)
SELECT
    case when a.m_week-b.m_week < 0 then 'NEW'
    else 'OLD'
    end gubun
     , a.code_n, a.no_p, a.f_date, a.m_date
     , a.m_week a_week
     , b.m_week b_week
     , a.qty    a_qty
     , b.qty    b_qty
     , LEAST( a.s_qty - b.s_qty + b.qty, b.s_qty - a.s_qty + a.qty, a.qty, b.qty) use_qty
  FROM (SELECT code_n, no_p, f_date, m_date, m_week, qty
             , SUM(qty) OVER(PARTITION BY code_n ORDER BY no_p) s_qty
          FROM t
          where code_n ='lg_sam'
        ) a
     , (SELECT code_n, m_week, qty
             , SUM(qty) OVER(PARTITION BY code_n ORDER BY m_week) s_qty
          FROM c
          where code_n ='lg_sam'
        ) b
 WHERE a.code_n = b.code_n
   AND a.s_qty > b.s_qty - b.qty
   AND b.s_qty > a.s_qty - a.qty
 ORDER BY code_n, no_p, f_date, a_week, b_week

 


by 마농 [2019.03.20 09:11:26]

1. 달력을 더 보여주세요. 1월만 보여주셨는데, 12월 마지막주와 2월 첫주도 보여주세요.
2. 제가 드린 쿼리는 lg_sam 과 LG_SAM 모두 5줄로 나오는데.
  - LG_SAM 의 두줄은 하나로 합쳐야 하는 건가요?
3. 빨간색 부분
  - 5번째 행에 표시되면서 m_date 가 그 주의 금요일로 표시되는 듯 한데요? 왜? 일요일이 아니고 금요일이죠?
  - 3번째 행에 표시하면서 m_date 가 그 주의 월요일로 표시되는게 더 좋을 것 같은데요.
4. 예시 정렬 순서 관련입니다.
  - 예시가 계속 순서가 뒤죽박죽이라 최종예시 기준으로 작성하였습니다.
  - no_p 순서대로 m_date, f_date 등이 정렬되어 있다고 가정하고 ORDER BY no_p 했습니다.
  - 그게 아니라면 ORDER BY m_date, f_date, no_p 식으로 해야 할 것 같네요.
  - m_date 가 주차의 기준일자인듯 하니 이 항목이 정렬의 최우선이 되어야 할 것 같네요.


by 캘린다 [2019.03.20 09:39:45]
1. 1월~12월 달력 코드입니다
SELECT  '20190101' m_date, '20190101' m_week FROM DUAL UNION ALL
SELECT  '20190102' m_date, '20190101' m_week FROM DUAL UNION ALL
SELECT  '20190103' m_date, '20190101' m_week FROM DUAL UNION ALL
SELECT  '20190104' m_date, '20190101' m_week FROM DUAL UNION ALL
SELECT  '20190105' m_date, '20190101' m_week FROM DUAL UNION ALL
SELECT  '20190106' m_date, '20190101' m_week FROM DUAL UNION ALL
SELECT  '20190107' m_date, '20190102' m_week FROM DUAL UNION ALL
SELECT  '20190108' m_date, '20190102' m_week FROM DUAL UNION ALL
SELECT  '20190109' m_date, '20190102' m_week FROM DUAL UNION ALL
SELECT  '20190110' m_date, '20190102' m_week FROM DUAL UNION ALL
SELECT  '20190111' m_date, '20190102' m_week FROM DUAL UNION ALL
SELECT  '20190112' m_date, '20190102' m_week FROM DUAL UNION ALL
SELECT  '20190113' m_date, '20190102' m_week FROM DUAL UNION ALL
SELECT  '20190114' m_date, '20190103' m_week FROM DUAL UNION ALL
SELECT  '20190115' m_date, '20190103' m_week FROM DUAL UNION ALL
SELECT  '20190116' m_date, '20190103' m_week FROM DUAL UNION ALL
SELECT  '20190117' m_date, '20190103' m_week FROM DUAL UNION ALL
SELECT  '20190118' m_date, '20190103' m_week FROM DUAL UNION ALL
SELECT  '20190119' m_date, '20190103' m_week FROM DUAL UNION ALL
SELECT  '20190120' m_date, '20190103' m_week FROM DUAL UNION ALL
SELECT  '20190121' m_date, '20190104' m_week FROM DUAL UNION ALL
SELECT  '20190122' m_date, '20190104' m_week FROM DUAL UNION ALL
SELECT  '20190123' m_date, '20190104' m_week FROM DUAL UNION ALL
SELECT  '20190124' m_date, '20190104' m_week FROM DUAL UNION ALL
SELECT  '20190125' m_date, '20190104' m_week FROM DUAL UNION ALL
SELECT  '20190126' m_date, '20190104' m_week FROM DUAL UNION ALL
SELECT  '20190127' m_date, '20190104' m_week FROM DUAL UNION ALL
SELECT  '20190128' m_date, '20190105' m_week FROM DUAL UNION ALL
SELECT  '20190129' m_date, '20190105' m_week FROM DUAL UNION ALL
SELECT  '20190130' m_date, '20190105' m_week FROM DUAL UNION ALL
SELECT  '20190131' m_date, '20190105' m_week FROM DUAL UNION ALL
SELECT  '20190201' m_date, '20190205' m_week FROM DUAL UNION ALL
SELECT  '20190202' m_date, '20190205' m_week FROM DUAL UNION ALL
SELECT  '20190203' m_date, '20190205' m_week FROM DUAL UNION ALL
SELECT  '20190204' m_date, '20190206' m_week FROM DUAL UNION ALL
SELECT  '20190205' m_date, '20190206' m_week FROM DUAL UNION ALL
SELECT  '20190206' m_date, '20190206' m_week FROM DUAL UNION ALL
SELECT  '20190207' m_date, '20190206' m_week FROM DUAL UNION ALL
SELECT  '20190208' m_date, '20190206' m_week FROM DUAL UNION ALL
SELECT  '20190209' m_date, '20190206' m_week FROM DUAL UNION ALL
SELECT  '20190210' m_date, '20190206' m_week FROM DUAL UNION ALL
SELECT  '20190211' m_date, '20190207' m_week FROM DUAL UNION ALL
SELECT  '20190212' m_date, '20190207' m_week FROM DUAL UNION ALL
SELECT  '20190213' m_date, '20190207' m_week FROM DUAL UNION ALL
SELECT  '20190214' m_date, '20190207' m_week FROM DUAL UNION ALL
SELECT  '20190215' m_date, '20190207' m_week FROM DUAL UNION ALL
SELECT  '20190216' m_date, '20190207' m_week FROM DUAL UNION ALL
SELECT  '20190217' m_date, '20190207' m_week FROM DUAL UNION ALL
SELECT  '20190218' m_date, '20190208' m_week FROM DUAL UNION ALL
SELECT  '20190219' m_date, '20190208' m_week FROM DUAL UNION ALL
SELECT  '20190220' m_date, '20190208' m_week FROM DUAL UNION ALL
SELECT  '20190221' m_date, '20190208' m_week FROM DUAL UNION ALL
SELECT  '20190222' m_date, '20190208' m_week FROM DUAL UNION ALL
SELECT  '20190223' m_date, '20190208' m_week FROM DUAL UNION ALL
SELECT  '20190224' m_date, '20190208' m_week FROM DUAL UNION ALL
SELECT  '20190225' m_date, '20190209' m_week FROM DUAL UNION ALL
SELECT  '20190226' m_date, '20190209' m_week FROM DUAL UNION ALL
SELECT  '20190227' m_date, '20190209' m_week FROM DUAL UNION ALL
SELECT  '20190228' m_date, '20190209' m_week FROM DUAL UNION ALL
SELECT  '20190301' m_date, '20190309' m_week FROM DUAL UNION ALL
SELECT  '20190302' m_date, '20190309' m_week FROM DUAL UNION ALL
SELECT  '20190303' m_date, '20190309' m_week FROM DUAL UNION ALL
SELECT  '20190304' m_date, '20190310' m_week FROM DUAL UNION ALL
SELECT  '20190305' m_date, '20190310' m_week FROM DUAL UNION ALL
SELECT  '20190306' m_date, '20190310' m_week FROM DUAL UNION ALL
SELECT  '20190307' m_date, '20190310' m_week FROM DUAL UNION ALL
SELECT  '20190308' m_date, '20190310' m_week FROM DUAL UNION ALL
SELECT  '20190309' m_date, '20190310' m_week FROM DUAL UNION ALL
SELECT  '20190310' m_date, '20190310' m_week FROM DUAL UNION ALL
SELECT  '20190311' m_date, '20190311' m_week FROM DUAL UNION ALL
SELECT  '20190312' m_date, '20190311' m_week FROM DUAL UNION ALL
SELECT  '20190313' m_date, '20190311' m_week FROM DUAL UNION ALL
SELECT  '20190314' m_date, '20190311' m_week FROM DUAL UNION ALL
SELECT  '20190315' m_date, '20190311' m_week FROM DUAL UNION ALL
SELECT  '20190316' m_date, '20190311' m_week FROM DUAL UNION ALL
SELECT  '20190317' m_date, '20190311' m_week FROM DUAL UNION ALL
SELECT  '20190318' m_date, '20190312' m_week FROM DUAL UNION ALL
SELECT  '20190319' m_date, '20190312' m_week FROM DUAL UNION ALL
SELECT  '20190320' m_date, '20190312' m_week FROM DUAL UNION ALL
SELECT  '20190321' m_date, '20190312' m_week FROM DUAL UNION ALL
SELECT  '20190322' m_date, '20190312' m_week FROM DUAL UNION ALL
SELECT  '20190323' m_date, '20190312' m_week FROM DUAL UNION ALL
SELECT  '20190324' m_date, '20190312' m_week FROM DUAL UNION ALL
SELECT  '20190325' m_date, '20190313' m_week FROM DUAL UNION ALL
SELECT  '20190326' m_date, '20190313' m_week FROM DUAL UNION ALL
SELECT  '20190327' m_date, '20190313' m_week FROM DUAL UNION ALL
SELECT  '20190328' m_date, '20190313' m_week FROM DUAL UNION ALL
SELECT  '20190329' m_date, '20190313' m_week FROM DUAL UNION ALL
SELECT  '20190330' m_date, '20190313' m_week FROM DUAL UNION ALL
SELECT  '20190331' m_date, '20190313' m_week FROM DUAL UNION ALL
SELECT  '20190401' m_date, '20190414' m_week FROM DUAL UNION ALL
SELECT  '20190402' m_date, '20190414' m_week FROM DUAL UNION ALL
SELECT  '20190403' m_date, '20190414' m_week FROM DUAL UNION ALL
SELECT  '20190404' m_date, '20190414' m_week FROM DUAL UNION ALL
SELECT  '20190405' m_date, '20190414' m_week FROM DUAL UNION ALL
SELECT  '20190406' m_date, '20190414' m_week FROM DUAL UNION ALL
SELECT  '20190407' m_date, '20190414' m_week FROM DUAL UNION ALL
SELECT  '20190408' m_date, '20190415' m_week FROM DUAL UNION ALL
SELECT  '20190409' m_date, '20190415' m_week FROM DUAL UNION ALL
SELECT  '20190410' m_date, '20190415' m_week FROM DUAL UNION ALL
SELECT  '20190411' m_date, '20190415' m_week FROM DUAL UNION ALL
SELECT  '20190412' m_date, '20190415' m_week FROM DUAL UNION ALL
SELECT  '20190413' m_date, '20190415' m_week FROM DUAL UNION ALL
SELECT  '20190414' m_date, '20190415' m_week FROM DUAL UNION ALL
SELECT  '20190415' m_date, '20190416' m_week FROM DUAL UNION ALL
SELECT  '20190416' m_date, '20190416' m_week FROM DUAL UNION ALL
SELECT  '20190417' m_date, '20190416' m_week FROM DUAL UNION ALL
SELECT  '20190418' m_date, '20190416' m_week FROM DUAL UNION ALL
SELECT  '20190419' m_date, '20190416' m_week FROM DUAL UNION ALL
SELECT  '20190420' m_date, '20190416' m_week FROM DUAL UNION ALL
SELECT  '20190421' m_date, '20190416' m_week FROM DUAL UNION ALL
SELECT  '20190422' m_date, '20190417' m_week FROM DUAL UNION ALL
SELECT  '20190423' m_date, '20190417' m_week FROM DUAL UNION ALL
SELECT  '20190424' m_date, '20190417' m_week FROM DUAL UNION ALL
SELECT  '20190425' m_date, '20190417' m_week FROM DUAL UNION ALL
SELECT  '20190426' m_date, '20190417' m_week FROM DUAL UNION ALL
SELECT  '20190427' m_date, '20190417' m_week FROM DUAL UNION ALL
SELECT  '20190428' m_date, '20190417' m_week FROM DUAL UNION ALL
SELECT  '20190429' m_date, '20190418' m_week FROM DUAL UNION ALL
SELECT  '20190430' m_date, '20190418' m_week FROM DUAL UNION ALL
SELECT  '20190501' m_date, '20190518' m_week FROM DUAL UNION ALL
SELECT  '20190502' m_date, '20190518' m_week FROM DUAL UNION ALL
SELECT  '20190503' m_date, '20190518' m_week FROM DUAL UNION ALL
SELECT  '20190504' m_date, '20190518' m_week FROM DUAL UNION ALL
SELECT  '20190505' m_date, '20190518' m_week FROM DUAL UNION ALL
SELECT  '20190506' m_date, '20190519' m_week FROM DUAL UNION ALL
SELECT  '20190507' m_date, '20190519' m_week FROM DUAL UNION ALL
SELECT  '20190508' m_date, '20190519' m_week FROM DUAL UNION ALL
SELECT  '20190509' m_date, '20190519' m_week FROM DUAL UNION ALL
SELECT  '20190510' m_date, '20190519' m_week FROM DUAL UNION ALL
SELECT  '20190511' m_date, '20190519' m_week FROM DUAL UNION ALL
SELECT  '20190512' m_date, '20190519' m_week FROM DUAL UNION ALL
SELECT  '20190513' m_date, '20190520' m_week FROM DUAL UNION ALL
SELECT  '20190514' m_date, '20190520' m_week FROM DUAL UNION ALL
SELECT  '20190515' m_date, '20190520' m_week FROM DUAL UNION ALL
SELECT  '20190516' m_date, '20190520' m_week FROM DUAL UNION ALL
SELECT  '20190517' m_date, '20190520' m_week FROM DUAL UNION ALL
SELECT  '20190518' m_date, '20190520' m_week FROM DUAL UNION ALL
SELECT  '20190519' m_date, '20190520' m_week FROM DUAL UNION ALL
SELECT  '20190520' m_date, '20190521' m_week FROM DUAL UNION ALL
SELECT  '20190521' m_date, '20190521' m_week FROM DUAL UNION ALL
SELECT  '20190522' m_date, '20190521' m_week FROM DUAL UNION ALL
SELECT  '20190523' m_date, '20190521' m_week FROM DUAL UNION ALL
SELECT  '20190524' m_date, '20190521' m_week FROM DUAL UNION ALL
SELECT  '20190525' m_date, '20190521' m_week FROM DUAL UNION ALL
SELECT  '20190526' m_date, '20190521' m_week FROM DUAL UNION ALL
SELECT  '20190527' m_date, '20190522' m_week FROM DUAL UNION ALL
SELECT  '20190528' m_date, '20190522' m_week FROM DUAL UNION ALL
SELECT  '20190529' m_date, '20190522' m_week FROM DUAL UNION ALL
SELECT  '20190530' m_date, '20190522' m_week FROM DUAL UNION ALL
SELECT  '20190531' m_date, '20190522' m_week FROM DUAL UNION ALL
SELECT  '20190601' m_date, '20190622' m_week FROM DUAL UNION ALL
SELECT  '20190602' m_date, '20190622' m_week FROM DUAL UNION ALL
SELECT  '20190603' m_date, '20190623' m_week FROM DUAL UNION ALL
SELECT  '20190604' m_date, '20190623' m_week FROM DUAL UNION ALL
SELECT  '20190605' m_date, '20190623' m_week FROM DUAL UNION ALL
SELECT  '20190606' m_date, '20190623' m_week FROM DUAL UNION ALL
SELECT  '20190607' m_date, '20190623' m_week FROM DUAL UNION ALL
SELECT  '20190608' m_date, '20190623' m_week FROM DUAL UNION ALL
SELECT  '20190609' m_date, '20190623' m_week FROM DUAL UNION ALL
SELECT  '20190610' m_date, '20190624' m_week FROM DUAL UNION ALL
SELECT  '20190611' m_date, '20190624' m_week FROM DUAL UNION ALL
SELECT  '20190612' m_date, '20190624' m_week FROM DUAL UNION ALL
SELECT  '20190613' m_date, '20190624' m_week FROM DUAL UNION ALL
SELECT  '20190614' m_date, '20190624' m_week FROM DUAL UNION ALL
SELECT  '20190615' m_date, '20190624' m_week FROM DUAL UNION ALL
SELECT  '20190616' m_date, '20190624' m_week FROM DUAL UNION ALL
SELECT  '20190617' m_date, '20190625' m_week FROM DUAL UNION ALL
SELECT  '20190618' m_date, '20190625' m_week FROM DUAL UNION ALL
SELECT  '20190619' m_date, '20190625' m_week FROM DUAL UNION ALL
SELECT  '20190620' m_date, '20190625' m_week FROM DUAL UNION ALL
SELECT  '20190621' m_date, '20190625' m_week FROM DUAL UNION ALL
SELECT  '20190622' m_date, '20190625' m_week FROM DUAL UNION ALL
SELECT  '20190623' m_date, '20190625' m_week FROM DUAL UNION ALL
SELECT  '20190624' m_date, '20190626' m_week FROM DUAL UNION ALL
SELECT  '20190625' m_date, '20190626' m_week FROM DUAL UNION ALL
SELECT  '20190626' m_date, '20190626' m_week FROM DUAL UNION ALL
SELECT  '20190627' m_date, '20190626' m_week FROM DUAL UNION ALL
SELECT  '20190628' m_date, '20190626' m_week FROM DUAL UNION ALL
SELECT  '20190629' m_date, '20190626' m_week FROM DUAL UNION ALL
SELECT  '20190630' m_date, '20190626' m_week FROM DUAL UNION ALL
SELECT  '20190701' m_date, '20190727' m_week FROM DUAL UNION ALL
SELECT  '20190702' m_date, '20190727' m_week FROM DUAL UNION ALL
SELECT  '20190703' m_date, '20190727' m_week FROM DUAL UNION ALL
SELECT  '20190704' m_date, '20190727' m_week FROM DUAL UNION ALL
SELECT  '20190705' m_date, '20190727' m_week FROM DUAL UNION ALL
SELECT  '20190706' m_date, '20190727' m_week FROM DUAL UNION ALL
SELECT  '20190707' m_date, '20190727' m_week FROM DUAL UNION ALL
SELECT  '20190708' m_date, '20190728' m_week FROM DUAL UNION ALL
SELECT  '20190709' m_date, '20190728' m_week FROM DUAL UNION ALL
SELECT  '20190710' m_date, '20190728' m_week FROM DUAL UNION ALL
SELECT  '20190711' m_date, '20190728' m_week FROM DUAL UNION ALL
SELECT  '20190712' m_date, '20190728' m_week FROM DUAL UNION ALL
SELECT  '20190713' m_date, '20190728' m_week FROM DUAL UNION ALL
SELECT  '20190714' m_date, '20190728' m_week FROM DUAL UNION ALL
SELECT  '20190715' m_date, '20190729' m_week FROM DUAL UNION ALL
SELECT  '20190716' m_date, '20190729' m_week FROM DUAL UNION ALL
SELECT  '20190717' m_date, '20190729' m_week FROM DUAL UNION ALL
SELECT  '20190718' m_date, '20190729' m_week FROM DUAL UNION ALL
SELECT  '20190719' m_date, '20190729' m_week FROM DUAL UNION ALL
SELECT  '20190720' m_date, '20190729' m_week FROM DUAL UNION ALL
SELECT  '20190721' m_date, '20190729' m_week FROM DUAL UNION ALL
SELECT  '20190722' m_date, '20190730' m_week FROM DUAL UNION ALL
SELECT  '20190723' m_date, '20190730' m_week FROM DUAL UNION ALL
SELECT  '20190724' m_date, '20190730' m_week FROM DUAL UNION ALL
SELECT  '20190725' m_date, '20190730' m_week FROM DUAL UNION ALL
SELECT  '20190726' m_date, '20190730' m_week FROM DUAL UNION ALL
SELECT  '20190727' m_date, '20190730' m_week FROM DUAL UNION ALL
SELECT  '20190728' m_date, '20190730' m_week FROM DUAL UNION ALL
SELECT  '20190729' m_date, '20190731' m_week FROM DUAL UNION ALL
SELECT  '20190730' m_date, '20190731' m_week FROM DUAL UNION ALL
SELECT  '20190731' m_date, '20190731' m_week FROM DUAL UNION ALL
SELECT  '20190801' m_date, '20190831' m_week FROM DUAL UNION ALL
SELECT  '20190802' m_date, '20190831' m_week FROM DUAL UNION ALL
SELECT  '20190803' m_date, '20190831' m_week FROM DUAL UNION ALL
SELECT  '20190804' m_date, '20190831' m_week FROM DUAL UNION ALL
SELECT  '20190805' m_date, '20190832' m_week FROM DUAL UNION ALL
SELECT  '20190806' m_date, '20190832' m_week FROM DUAL UNION ALL
SELECT  '20190807' m_date, '20190832' m_week FROM DUAL UNION ALL
SELECT  '20190808' m_date, '20190832' m_week FROM DUAL UNION ALL
SELECT  '20190809' m_date, '20190832' m_week FROM DUAL UNION ALL
SELECT  '20190810' m_date, '20190832' m_week FROM DUAL UNION ALL
SELECT  '20190811' m_date, '20190832' m_week FROM DUAL UNION ALL
SELECT  '20190812' m_date, '20190833' m_week FROM DUAL UNION ALL
SELECT  '20190813' m_date, '20190833' m_week FROM DUAL UNION ALL
SELECT  '20190814' m_date, '20190833' m_week FROM DUAL UNION ALL
SELECT  '20190815' m_date, '20190833' m_week FROM DUAL UNION ALL
SELECT  '20190816' m_date, '20190833' m_week FROM DUAL UNION ALL
SELECT  '20190817' m_date, '20190833' m_week FROM DUAL UNION ALL
SELECT  '20190818' m_date, '20190833' m_week FROM DUAL UNION ALL
SELECT  '20190819' m_date, '20190834' m_week FROM DUAL UNION ALL
SELECT  '20190820' m_date, '20190834' m_week FROM DUAL UNION ALL
SELECT  '20190821' m_date, '20190834' m_week FROM DUAL UNION ALL
SELECT  '20190822' m_date, '20190834' m_week FROM DUAL UNION ALL
SELECT  '20190823' m_date, '20190834' m_week FROM DUAL UNION ALL
SELECT  '20190824' m_date, '20190834' m_week FROM DUAL UNION ALL
SELECT  '20190825' m_date, '20190834' m_week FROM DUAL UNION ALL
SELECT  '20190826' m_date, '20190835' m_week FROM DUAL UNION ALL
SELECT  '20190827' m_date, '20190835' m_week FROM DUAL UNION ALL
SELECT  '20190828' m_date, '20190835' m_week FROM DUAL UNION ALL
SELECT  '20190829' m_date, '20190835' m_week FROM DUAL UNION ALL
SELECT  '20190830' m_date, '20190835' m_week FROM DUAL UNION ALL
SELECT  '20190831' m_date, '20190835' m_week FROM DUAL UNION ALL
SELECT  '20190901' m_date, '20190935' m_week FROM DUAL UNION ALL
SELECT  '20190902' m_date, '20190936' m_week FROM DUAL UNION ALL
SELECT  '20190903' m_date, '20190936' m_week FROM DUAL UNION ALL
SELECT  '20190904' m_date, '20190936' m_week FROM DUAL UNION ALL
SELECT  '20190905' m_date, '20190936' m_week FROM DUAL UNION ALL
SELECT  '20190906' m_date, '20190936' m_week FROM DUAL UNION ALL
SELECT  '20190907' m_date, '20190936' m_week FROM DUAL UNION ALL
SELECT  '20190908' m_date, '20190936' m_week FROM DUAL UNION ALL
SELECT  '20190909' m_date, '20190937' m_week FROM DUAL UNION ALL
SELECT  '20190910' m_date, '20190937' m_week FROM DUAL UNION ALL
SELECT  '20190911' m_date, '20190937' m_week FROM DUAL UNION ALL
SELECT  '20190912' m_date, '20190937' m_week FROM DUAL UNION ALL
SELECT  '20190913' m_date, '20190937' m_week FROM DUAL UNION ALL
SELECT  '20190914' m_date, '20190937' m_week FROM DUAL UNION ALL
SELECT  '20190915' m_date, '20190937' m_week FROM DUAL UNION ALL
SELECT  '20190916' m_date, '20190938' m_week FROM DUAL UNION ALL
SELECT  '20190917' m_date, '20190938' m_week FROM DUAL UNION ALL
SELECT  '20190918' m_date, '20190938' m_week FROM DUAL UNION ALL
SELECT  '20190919' m_date, '20190938' m_week FROM DUAL UNION ALL
SELECT  '20190920' m_date, '20190938' m_week FROM DUAL UNION ALL
SELECT  '20190921' m_date, '20190938' m_week FROM DUAL UNION ALL
SELECT  '20190922' m_date, '20190938' m_week FROM DUAL UNION ALL
SELECT  '20190923' m_date, '20190939' m_week FROM DUAL UNION ALL
SELECT  '20190924' m_date, '20190939' m_week FROM DUAL UNION ALL
SELECT  '20190925' m_date, '20190939' m_week FROM DUAL UNION ALL
SELECT  '20190926' m_date, '20190939' m_week FROM DUAL UNION ALL
SELECT  '20190927' m_date, '20190939' m_week FROM DUAL UNION ALL
SELECT  '20190928' m_date, '20190939' m_week FROM DUAL UNION ALL
SELECT  '20190929' m_date, '20190939' m_week FROM DUAL UNION ALL
SELECT  '20190930' m_date, '20190940' m_week FROM DUAL UNION ALL
SELECT  '20191001' m_date, '20191040' m_week FROM DUAL UNION ALL
SELECT  '20191002' m_date, '20191040' m_week FROM DUAL UNION ALL
SELECT  '20191003' m_date, '20191040' m_week FROM DUAL UNION ALL
SELECT  '20191004' m_date, '20191040' m_week FROM DUAL UNION ALL
SELECT  '20191005' m_date, '20191040' m_week FROM DUAL UNION ALL
SELECT  '20191006' m_date, '20191040' m_week FROM DUAL UNION ALL
SELECT  '20191007' m_date, '20191041' m_week FROM DUAL UNION ALL
SELECT  '20191008' m_date, '20191041' m_week FROM DUAL UNION ALL
SELECT  '20191009' m_date, '20191041' m_week FROM DUAL UNION ALL
SELECT  '20191010' m_date, '20191041' m_week FROM DUAL UNION ALL
SELECT  '20191011' m_date, '20191041' m_week FROM DUAL UNION ALL
SELECT  '20191012' m_date, '20191041' m_week FROM DUAL UNION ALL
SELECT  '20191013' m_date, '20191041' m_week FROM DUAL UNION ALL
SELECT  '20191014' m_date, '20191042' m_week FROM DUAL UNION ALL
SELECT  '20191015' m_date, '20191042' m_week FROM DUAL UNION ALL
SELECT  '20191016' m_date, '20191042' m_week FROM DUAL UNION ALL
SELECT  '20191017' m_date, '20191042' m_week FROM DUAL UNION ALL
SELECT  '20191018' m_date, '20191042' m_week FROM DUAL UNION ALL
SELECT  '20191019' m_date, '20191042' m_week FROM DUAL UNION ALL
SELECT  '20191020' m_date, '20191042' m_week FROM DUAL UNION ALL
SELECT  '20191021' m_date, '20191043' m_week FROM DUAL UNION ALL
SELECT  '20191022' m_date, '20191043' m_week FROM DUAL UNION ALL
SELECT  '20191023' m_date, '20191043' m_week FROM DUAL UNION ALL
SELECT  '20191024' m_date, '20191043' m_week FROM DUAL UNION ALL
SELECT  '20191025' m_date, '20191043' m_week FROM DUAL UNION ALL
SELECT  '20191026' m_date, '20191043' m_week FROM DUAL UNION ALL
SELECT  '20191027' m_date, '20191043' m_week FROM DUAL UNION ALL
SELECT  '20191028' m_date, '20191044' m_week FROM DUAL UNION ALL
SELECT  '20191029' m_date, '20191044' m_week FROM DUAL UNION ALL
SELECT  '20191030' m_date, '20191044' m_week FROM DUAL UNION ALL
SELECT  '20191031' m_date, '20191044' m_week FROM DUAL UNION ALL
SELECT  '20191101' m_date, '20191144' m_week FROM DUAL UNION ALL
SELECT  '20191102' m_date, '20191144' m_week FROM DUAL UNION ALL
SELECT  '20191103' m_date, '20191144' m_week FROM DUAL UNION ALL
SELECT  '20191104' m_date, '20191145' m_week FROM DUAL UNION ALL
SELECT  '20191105' m_date, '20191145' m_week FROM DUAL UNION ALL
SELECT  '20191106' m_date, '20191145' m_week FROM DUAL UNION ALL
SELECT  '20191107' m_date, '20191145' m_week FROM DUAL UNION ALL
SELECT  '20191108' m_date, '20191145' m_week FROM DUAL UNION ALL
SELECT  '20191109' m_date, '20191145' m_week FROM DUAL UNION ALL
SELECT  '20191110' m_date, '20191145' m_week FROM DUAL UNION ALL
SELECT  '20191111' m_date, '20191146' m_week FROM DUAL UNION ALL
SELECT  '20191112' m_date, '20191146' m_week FROM DUAL UNION ALL
SELECT  '20191113' m_date, '20191146' m_week FROM DUAL UNION ALL
SELECT  '20191114' m_date, '20191146' m_week FROM DUAL UNION ALL
SELECT  '20191115' m_date, '20191146' m_week FROM DUAL UNION ALL
SELECT  '20191116' m_date, '20191146' m_week FROM DUAL UNION ALL
SELECT  '20191117' m_date, '20191146' m_week FROM DUAL UNION ALL
SELECT  '20191118' m_date, '20191147' m_week FROM DUAL UNION ALL
SELECT  '20191119' m_date, '20191147' m_week FROM DUAL UNION ALL
SELECT  '20191120' m_date, '20191147' m_week FROM DUAL UNION ALL
SELECT  '20191121' m_date, '20191147' m_week FROM DUAL UNION ALL
SELECT  '20191122' m_date, '20191147' m_week FROM DUAL UNION ALL
SELECT  '20191123' m_date, '20191147' m_week FROM DUAL UNION ALL
SELECT  '20191124' m_date, '20191147' m_week FROM DUAL UNION ALL
SELECT  '20191125' m_date, '20191148' m_week FROM DUAL UNION ALL
SELECT  '20191126' m_date, '20191148' m_week FROM DUAL UNION ALL
SELECT  '20191127' m_date, '20191148' m_week FROM DUAL UNION ALL
SELECT  '20191128' m_date, '20191148' m_week FROM DUAL UNION ALL
SELECT  '20191129' m_date, '20191148' m_week FROM DUAL UNION ALL
SELECT  '20191130' m_date, '20191148' m_week FROM DUAL UNION ALL
SELECT  '20191201' m_date, '20191248' m_week FROM DUAL UNION ALL
SELECT  '20191202' m_date, '20191249' m_week FROM DUAL UNION ALL
SELECT  '20191203' m_date, '20191249' m_week FROM DUAL UNION ALL
SELECT  '20191204' m_date, '20191249' m_week FROM DUAL UNION ALL
SELECT  '20191205' m_date, '20191249' m_week FROM DUAL UNION ALL
SELECT  '20191206' m_date, '20191249' m_week FROM DUAL UNION ALL
SELECT  '20191207' m_date, '20191249' m_week FROM DUAL UNION ALL
SELECT  '20191208' m_date, '20191249' m_week FROM DUAL UNION ALL
SELECT  '20191209' m_date, '20191250' m_week FROM DUAL UNION ALL
SELECT  '20191210' m_date, '20191250' m_week FROM DUAL UNION ALL
SELECT  '20191211' m_date, '20191250' m_week FROM DUAL UNION ALL
SELECT  '20191212' m_date, '20191250' m_week FROM DUAL UNION ALL
SELECT  '20191213' m_date, '20191250' m_week FROM DUAL UNION ALL
SELECT  '20191214' m_date, '20191250' m_week FROM DUAL UNION ALL
SELECT  '20191215' m_date, '20191250' m_week FROM DUAL UNION ALL
SELECT  '20191216' m_date, '20191251' m_week FROM DUAL UNION ALL
SELECT  '20191217' m_date, '20191251' m_week FROM DUAL UNION ALL
SELECT  '20191218' m_date, '20191251' m_week FROM DUAL UNION ALL
SELECT  '20191219' m_date, '20191251' m_week FROM DUAL UNION ALL
SELECT  '20191220' m_date, '20191251' m_week FROM DUAL UNION ALL
SELECT  '20191221' m_date, '20191251' m_week FROM DUAL UNION ALL
SELECT  '20191222' m_date, '20191251' m_week FROM DUAL UNION ALL
SELECT  '20191223' m_date, '20191252' m_week FROM DUAL UNION ALL
SELECT  '20191224' m_date, '20191252' m_week FROM DUAL UNION ALL
SELECT  '20191225' m_date, '20191252' m_week FROM DUAL UNION ALL
SELECT  '20191226' m_date, '20191252' m_week FROM DUAL UNION ALL
SELECT  '20191227' m_date, '20191252' m_week FROM DUAL UNION ALL
SELECT  '20191228' m_date, '20191252' m_week FROM DUAL UNION ALL
SELECT  '20191229' m_date, '20191252' m_week FROM DUAL UNION ALL
SELECT  '20191230' m_date, '20191253' m_week FROM DUAL UNION ALL
SELECT  '20191231' m_date, '20191253' m_week FROM DUAL 

2. LG_SAM에서 2주차 조건이 50이남는데 이 조건50이 3주차로 누적이되지않고 원본 3주차 100에서 50을 b_week가 3주에서 > 2주로 변경이되고 50을 표기합니다.

3. 주말은 빨간날이라 평일의 마지막날인 금요일로 지정하였습니다. 금요일이 어렵다면 월요일로 표기되어도 괜찮습니다.

4. 네알겠습니다.

5. 다른 문제가잇는데 4번인거 같기도하고, NO_P가 같은데 M_date와 M_WEEK가 서로다릅니다.

예)

CODE_N NO_P F_DATE M_DATE M_WEEK QTY
lg_sam 521101-04 20180104 20190108 20190102 100
lg_sam 521101-04 20180104 20190114 20190103 100

이런 경우에 DATA가 꼬이는것 같습니다.

결과 표

GUBUN CODE_N NO_P F_DATE M_DATE A_WEEK B_WEEK A_QTY B_QTY USE_QTY 결과값 남은 조건값  
OLD LG-SAM 521102-01 20180105 20190107 20190102 20190102 100 350 100 100 250  
OLD LG-SAM 521102-02 20180106 20190108 20190102 20190102 100 350 100 100 150  
OLD LG-SAM 521102-03 20180107 20190109 20190102 20190102 100 350 100 100 50  
OLD LG-SAM 521102-04 20180108 20190114 20190103 20190102 100 350 50 50 50 조건이 누적되지않고 주차를 앞당김
OLD LG-SAM 521102-04 20180108 20190114 20190103 20190103 100 50 50 50 0  
OLD lg_sam 521101-01 20180101 20190101 20190101 20190101 100 150 100 100 50  
OLD lg_sam 521101-02 20180102 20190102 20190101 20190101 100 150 50 50 0  
NEW lg_sam 521101-02 20180102 20190102 20190101 20190102 100 250 50 50 200  
OLD lg_sam 521101-03 20180103 20190107 20190102 20190102 100 250 100 100 100  
NEW lg_sam 521101-04 20180104 20190108 20190102 20190103 100 150 100 100 100 (OLD인데 NEW로 표기되고 주차가 바뀜)
OLD lg_sam 521101-04 20180104 20190114 20190103 20190103 100 150 100 100 50  
NEW lg_sam 521101-05 20180105 20190108 20190102 20190103 50 150 50 50 0  

 

order by 말씀해주신데로 3가지로 넣엇더니 꼬이는것 없이 뽑히는것 같습니다.

하지만 반대로 조건이 남는경우는 조건값이 남는 주차로 당겨집니다.


by 마농 [2019.03.20 09:48:38]

스크롤의 압박이 옵니다. 달력 중간은 좀 지워 주시고요.
년이 바뀌는 부분이 어떤 식인지 확인이 필요합니다.
20181231 와 20200101 의 달력도 보여주세요.


by 캘린다 [2019.03.20 10:08:46]
     SELECT , '20181201' m_date, '20181248' m_week FROM DUAL UNION ALL
     SELECT , '20181202' m_date, '20181248' m_week FROM DUAL UNION ALL
     SELECT , '20181203' m_date, '20181249' m_week FROM DUAL UNION ALL
     SELECT , '20181204' m_date, '20181249' m_week FROM DUAL UNION ALL
     SELECT , '20181205' m_date, '20181249' m_week FROM DUAL UNION ALL
     SELECT , '20181206' m_date, '20181249' m_week FROM DUAL UNION ALL
     SELECT , '20181207' m_date, '20181249' m_week FROM DUAL UNION ALL
     SELECT , '20181208' m_date, '20181249' m_week FROM DUAL UNION ALL
     SELECT , '20181209' m_date, '20181249' m_week FROM DUAL UNION ALL
     SELECT , '20181210' m_date, '20181250' m_week FROM DUAL UNION ALL
     SELECT , '20181211' m_date, '20181250' m_week FROM DUAL UNION ALL
     SELECT , '20181212' m_date, '20181250' m_week FROM DUAL UNION ALL
     SELECT , '20181213' m_date, '20181250' m_week FROM DUAL UNION ALL
     SELECT , '20181214' m_date, '20181250' m_week FROM DUAL UNION ALL
     SELECT , '20181215' m_date, '20181250' m_week FROM DUAL UNION ALL
     SELECT , '20181216' m_date, '20181250' m_week FROM DUAL UNION ALL
     SELECT , '20181217' m_date, '20181251' m_week FROM DUAL UNION ALL
     SELECT , '20181218' m_date, '20181251' m_week FROM DUAL UNION ALL
     SELECT , '20181219' m_date, '20181251' m_week FROM DUAL UNION ALL
     SELECT , '20181220' m_date, '20181251' m_week FROM DUAL UNION ALL
     SELECT , '20181221' m_date, '20181251' m_week FROM DUAL UNION ALL
     SELECT , '20181222' m_date, '20181251' m_week FROM DUAL UNION ALL
     SELECT , '20181223' m_date, '20181251' m_week FROM DUAL UNION ALL
     SELECT , '20181224' m_date, '20181252' m_week FROM DUAL UNION ALL
     SELECT , '20181225' m_date, '20181252' m_week FROM DUAL UNION ALL
     SELECT , '20181226' m_date, '20181252' m_week FROM DUAL UNION ALL
     SELECT , '20181227' m_date, '20181252' m_week FROM DUAL UNION ALL
     SELECT , '20181228' m_date, '20181252' m_week FROM DUAL UNION ALL
     SELECT , '20181229' m_date, '20181252' m_week FROM DUAL UNION ALL
     SELECT , '20181230' m_date, '20181252' m_week FROM DUAL UNION ALL
     SELECT , '20181231' m_date, '20181253' m_week FROM DUAL UNION ALL
     SELECT , '20190101' m_date, '20190101' m_week FROM DUAL UNION ALL
     SELECT , '20190102' m_date, '20190101' m_week FROM DUAL UNION ALL
     SELECT , '20190103' m_date, '20190101' m_week FROM DUAL UNION ALL
     SELECT , '20190104' m_date, '20190101' m_week FROM DUAL UNION ALL
     SELECT , '20190105' m_date, '20190101' m_week FROM DUAL UNION ALL
     SELECT , '20190106' m_date, '20190101' m_week FROM DUAL UNION ALL
     SELECT , '20190107' m_date, '20190102' m_week FROM DUAL UNION ALL
     SELECT , '20190108' m_date, '20190102' m_week FROM DUAL UNION ALL
     SELECT , '20190109' m_date, '20190102' m_week FROM DUAL UNION ALL
     SELECT , '20190110' m_date, '20190102' m_week FROM DUAL UNION ALL
     SELECT , '20190111' m_date, '20190102' m_week FROM DUAL UNION ALL
     SELECT , '20190112' m_date, '20190102' m_week FROM DUAL UNION ALL
     SELECT , '20190113' m_date, '20190102' m_week FROM DUAL UNION ALL
     SELECT , '20190114' m_date, '20190103' m_week FROM DUAL UNION ALL
     SELECT , '20190115' m_date, '20190103' m_week FROM DUAL UNION ALL
     SELECT , '20190116' m_date, '20190103' m_week FROM DUAL UNION ALL
     SELECT , '20190117' m_date, '20190103' m_week FROM DUAL UNION ALL
     SELECT , '20190118' m_date, '20190103' m_week FROM DUAL UNION ALL
     SELECT , '20190119' m_date, '20190103' m_week FROM DUAL UNION ALL
     SELECT , '20190120' m_date, '20190103' m_week FROM DUAL UNION ALL
     SELECT , '20190121' m_date, '20190104' m_week FROM DUAL UNION ALL
     SELECT , '20190122' m_date, '20190104' m_week FROM DUAL UNION ALL
     SELECT , '20190123' m_date, '20190104' m_week FROM DUAL UNION ALL
     SELECT , '20190124' m_date, '20190104' m_week FROM DUAL UNION ALL
     SELECT , '20190125' m_date, '20190104' m_week FROM DUAL UNION ALL
     SELECT , '20190126' m_date, '20190104' m_week FROM DUAL UNION ALL
     SELECT , '20190127' m_date, '20190104' m_week FROM DUAL UNION ALL
     SELECT , '20190128' m_date, '20190105' m_week FROM DUAL UNION ALL
     SELECT , '20190129' m_date, '20190105' m_week FROM DUAL UNION ALL
     SELECT , '20190130' m_date, '20190105' m_week FROM DUAL UNION ALL
     SELECT , '20190131' m_date, '20190105' m_week FROM DUAL UNION ALL
     SELECT , '20200101' m_date, '20200101' m_week FROM DUAL UNION ALL
     SELECT , '20200102' m_date, '20200101' m_week FROM DUAL UNION ALL
     SELECT , '20200103' m_date, '20200101' m_week FROM DUAL UNION ALL
     SELECT , '20200104' m_date, '20200101' m_week FROM DUAL UNION ALL
     SELECT , '20200105' m_date, '20200101' m_week FROM DUAL UNION ALL
     SELECT , '20200106' m_date, '20200102' m_week FROM DUAL UNION ALL
     SELECT , '20200107' m_date, '20200102' m_week FROM DUAL UNION ALL
     SELECT , '20200108' m_date, '20200102' m_week FROM DUAL UNION ALL
     SELECT , '20200109' m_date, '20200102' m_week FROM DUAL UNION ALL
     SELECT , '20200110' m_date, '20200102' m_week FROM DUAL UNION ALL
     SELECT , '20200111' m_date, '20200102' m_week FROM DUAL UNION ALL
     SELECT , '20200112' m_date, '20200102' m_week FROM DUAL UNION ALL
     SELECT , '20200113' m_date, '20200103' m_week FROM DUAL UNION ALL
     SELECT , '20200114' m_date, '20200103' m_week FROM DUAL UNION ALL
     SELECT , '20200115' m_date, '20200103' m_week FROM DUAL UNION ALL
     SELECT , '20200116' m_date, '20200103' m_week FROM DUAL UNION ALL
     SELECT , '20200117' m_date, '20200103' m_week FROM DUAL UNION ALL
     SELECT , '20200118' m_date, '20200103' m_week FROM DUAL UNION ALL
     SELECT , '20200119' m_date, '20200103' m_week FROM DUAL UNION ALL
     SELECT , '20200120' m_date, '20200104' m_week FROM DUAL UNION ALL
     SELECT , '20200121' m_date, '20200104' m_week FROM DUAL UNION ALL
     SELECT , '20200122' m_date, '20200104' m_week FROM DUAL UNION ALL
     SELECT , '20200123' m_date, '20200104' m_week FROM DUAL UNION ALL
     SELECT , '20200124' m_date, '20200104' m_week FROM DUAL UNION ALL
     SELECT , '20200125' m_date, '20200104' m_week FROM DUAL UNION ALL
     SELECT , '20200126' m_date, '20200104' m_week FROM DUAL UNION ALL
     SELECT , '20200127' m_date, '20200105' m_week FROM DUAL UNION ALL
     SELECT , '20200128' m_date, '20200105' m_week FROM DUAL UNION ALL
     SELECT , '20200129' m_date, '20200105' m_week FROM DUAL UNION ALL
     SELECT , '20200130' m_date, '20200105' m_week FROM DUAL UNION ALL
     SELECT , '20200131' m_date, '20200105' m_week FROM DUAL 

코드입니다.


by 마농 [2019.03.20 10:25:14]

20181231 과 20190101 은 일주일 단위로 보면 같은 주차인데 년도가 달라서 다른 주차가 되는 군요.
20181231 - 20181253
20190101 - 20190101
20190131 과 20190201 은 일주일 단위로 보면 같은 주차이고
연도의 주차로도 같은 주차(05 주차) 인데 월이 달라서 다른 주차가 되는 거네요.
20190131 - 20190105
20190201 - 20190205

한도 기준관리도 이렇게 다른 주차로 관리되고 있는 건가요?
같은 5주차라도 1월의 5주차(월화수목 4일간)과 2월의 5주차(금토일 3일간)이
각각의 한도를 가지는 형태가 되는 건가요?
이렇게 되면 주의 시작일이 항상 월요일이 아닐 수도 있겠네요.


by 캘린다 [2019.03.20 10:32:34]

같은 5주차로 봐야합니다.

M_week에서

년/월/주차로 표기하였는데

월을 제외한

년주차 로 해야할 것 같네요.

월이바뀌어도 해당주차는 그냥 같은 주차로 볼 수 있도록


by 마농 [2019.03.20 10:55:48]

1. 그럼 테이블 구조를 다 바꾸겠다는 건가요? yyyymmww --> yyyyww      
2. 년도는 유지인가요? 20181231 과 20190101 을 다른 주차로 보는 건가요?


by 캘린다 [2019.03.20 11:18:11]

1.yyyymmww를 유지한 상태로 동일 주차로 볼 수가 있나요?  안된다면 yyyyww로 해야 할 것 같습니다..

2.년도는 유지입니다. 하지만. 조건이 초과되거나 남는경우 20190101로 넘어가야됩니다.


by 마농 [2019.03.20 11:20:43]

잘라서 비교하면 동일 주차로 볼 수는 있지만 인덱스 스캔이 안되겠지요.


by 마농 [2019.03.20 11:27:33]
WITH t AS
(
SELECT 'lg_sam' code_n, '521101-01' no_p, '20180101' f_date, '20190101' m_date, '20190101' m_week, 100 qty FROM dual
UNION ALL SELECT 'lg_sam', '521101-02', '20180102', '20190102', '20190101', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-03', '20180103', '20190107', '20190102', 100 FROM dual
UNION ALL SELECT 'lg_sam', '521101-04', '20180104', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-01', '20180105', '20190107', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-02', '20180106', '20190108', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-03', '20180107', '20190109', '20190102', 100 FROM dual
UNION ALL SELECT 'LG-SAM', '521102-04', '20180108', '20190114', '20190103', 100 FROM dual
)
, c AS
(
SELECT 'lg_sam' code_n, '20190101' m_week, 150 qty FROM dual
UNION ALL SELECT 'lg_sam', '20190102', 250 FROM dual
UNION ALL SELECT 'lg_sam', '20190103', 150 FROM dual
UNION ALL SELECT 'LG-SAM', '20190102', 350 FROM dual
UNION ALL SELECT 'LG-SAM', '20190103',  50 FROM dual
)
, calendar AS
(
SELECT '20190101' d_date, '20190101' d_week FROM dual
UNION ALL SELECT '20190102', '20190101' FROM dual
UNION ALL SELECT '20190103', '20190101' FROM dual
UNION ALL SELECT '20190104', '20190101' FROM dual
UNION ALL SELECT '20190105', '20190101' FROM dual
UNION ALL SELECT '20190106', '20190101' FROM dual
UNION ALL SELECT '20190107', '20190102' FROM dual
UNION ALL SELECT '20190108', '20190102' FROM dual
UNION ALL SELECT '20190109', '20190102' FROM dual
UNION ALL SELECT '20190110', '20190102' FROM dual
UNION ALL SELECT '20190111', '20190102' FROM dual
UNION ALL SELECT '20190112', '20190102' FROM dual
UNION ALL SELECT '20190113', '20190102' FROM dual
UNION ALL SELECT '20190114', '20190103' FROM dual
)

SELECT a.code_n
     , a.no_p
     , a.f_date
     , DECODE(gb, 'NEW', c.d_date, a.m_date) m_date
     , a.gb
     , a.m_week
     , SUM(a.use_qty) use_qty
  FROM (SELECT a.code_n, a.no_p, a.f_date, a.m_date
             , CASE WHEN a.m_week < b.m_week THEN 'NEW'    ELSE 'OLD'    END gb
             , CASE WHEN a.m_week < b.m_week THEN b.m_week ELSE a.m_week END m_week
             , LEAST( a.s_qty - b.s_qty + b.qty
                    , b.s_qty - a.s_qty + a.qty
                    , a.qty
                    , b.qty
                    ) use_qty
          FROM (SELECT code_n, no_p, f_date, m_date, m_week, qty
                     , SUM(qty) OVER(PARTITION BY code_n ORDER BY m_date, f_date, no_p) s_qty
                  FROM t
                ) a
             , (SELECT code_n, m_week, qty
                     , SUM(qty) OVER(PARTITION BY code_n ORDER BY m_week) s_qty
                  FROM c
                ) b
         WHERE a.code_n = b.code_n
           AND a.s_qty > b.s_qty - b.qty
           AND b.s_qty > a.s_qty - a.qty
        ) a
     , (SELECT d_date
             , SUBSTR(d_week, 1, 4) yyyy
             , SUBSTR(d_week, 7, 2) ww
             , ROW_NUMBER() OVER(PARTITION BY SUBSTR(d_week, 1, 4), SUBSTR(d_week, 7, 2) ORDER BY d_date) rn
          FROM calendar
        ) c
 WHERE a.m_week LIKE c.yyyy || '__' || c.ww
   AND c.rn = 1
 GROUP BY a.code_n, DECODE(a.gb, 'NEW', c.d_date, a.m_date), a.f_date, a.no_p, a.gb, a.m_week
 ORDER BY code_n, m_date, f_date, no_p
;

 


by 캘린다 [2019.03.20 14:23:44]

감사합니다

모르는부분이 생기면 한 번 더 질문하겠습니다 ( .. )꾸벅

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입