WITH t AS ( SELECT 'A' name, '201807' month, 50 quantity, 100 quantity_all FROM dual UNION ALL SELECT 'A', '201808', 50, 0 FROM dual UNION ALL SELECT 'A', '201809', 50, 10 FROM dual UNION ALL SELECT 'B', '201807', 50, 50 FROM dual UNION ALL SELECT 'B', '201808', 50, 10 FROM dual UNION ALL SELECT 'B', '201810', 50, 20 FROM dual UNION ALL SELECT 'B', '201811', 50, 50 FROM dual UNION ALL SELECT 'B', '201812', 50, 50 FROM dual ) SELECT name , quarter , SUM(quantity) quantity , SUM(quantity_all) quantity_all , MAX(quantity_complete) quantity_complete FROM ( SELECT name , quarter , quantity , quantity_all , CASE WHEN SUM(quantity) OVER(PARTITION BY name, quarter ORDER BY month) <= SUM(quantity_all) OVER(PARTITION BY name, quarter) THEN month END quantity_complete FROM ( SELECT name, month, quantity, quantity_all, TO_CHAR(TO_DATE(month, 'yyyymm'), 'yyyy"0"q') quarter FROM t ) ) GROUP BY name, quarter ORDER BY name, quarter
SELECT name , quarter , SUM(quantity) quantity , SUM(quantity_all) quantity_all , MAX(quantity_complete) quantity_complete FROM (SELECT name , quarter , quantity , quantity_all -- 기존쿼리 : -- , CASE WHEN SUM(quantity ) OVER(PARTITION BY name, quarter ORDER BY month) -- <= SUM(quantity_all) OVER(PARTITION BY name, quarter) -- THEN month END quantity_complete -- 수정쿼리 : 수량 차감(- quantity) 추가 및 부등호 변경("<=" --> "<") , CASE WHEN SUM(quantity ) OVER(PARTITION BY name, quarter ORDER BY month) - quantity < SUM(quantity_all) OVER(PARTITION BY name, quarter) THEN month END quantity_complete FROM (SELECT name, month, quantity, quantity_all , TO_CHAR(TO_DATE(month, 'yyyymm'), 'yyyy"0"q') quarter FROM t ) ) GROUP BY name, quarter ORDER BY name, quarter ;
문제 해결을 위한 접근 방법이 잘못된 듯 합니다.
quarter 를 month 로 대체하여 문제가 된 듯 합니다.
quarter 를 month 로 대체하는게 아니라 그냥 quarter 만 제거하면 될 것 같네요.
부등호를 거꾸로 하면서 max 를 min 으로 바꿨습니다.
SELECT name , SUM(quantity) quantity , SUM(quantity_all) quantity_all , MIN(quantity_complete) quantity_complete FROM (SELECT name , quantity , quantity_all , CASE WHEN SUM(quantity ) OVER(PARTITION BY name ORDER BY month) >= SUM(quantity_all) OVER(PARTITION BY name) THEN month END quantity_complete FROM t ) GROUP BY name ORDER BY name ;