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

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

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

결과값

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 15:06:31]

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

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

원본

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

결과

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

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 13:24:55]

-- 조건 추가해 보세요.
AND SUBSTR(a.m_week, 1, 6) = SUBSTR(b.m_week, 1, 6)

by 마농 [2019.03.18 17:58:57]

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

by 마농 [2019.03.19 09:46:37]

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

by 마농 [2019.03.19 10:35:24]

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

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

1. F_DATE 가 빠른 데이타 부터 조건 데이타를 사용하고 조건데이타가 남는경우 다음주차 조건데이타에 누적

결과

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

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]

별도로 표현되야합니다

by 마농 [2019.03.20 08:08:59]

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

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: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

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:55:48]

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

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

1.yyyymmww를 유지한 상태로 동일 주차로 볼 수가 있나요? 안된다면 yyyyww

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
;

댓글등록

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

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

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

데이터베이스 질문과 답변