WITH sales(ites_NAME, s_month, s_qty, s_qty_all ) AS --Quantity_ALL ( select 'A', '201807' , 50 ,100 from dual union ALL select 'B', '201807' , 50 ,50 from dual union all select 'B', '201807' , 50 ,10 from dual union all select 'A', '201808' , 50 ,0 from dual union all select 'A', '201808' , 50 ,0 from dual union all select 'A', '201809' , 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 ites_name, year_qtr, SUM(s_qty) s_qty, SUM(s_qty_all) s_qty_all , to_char(to_date(min(s_month),'YYYYMM'),'MM"월"') AS quantity_complete FROM ( SELECT t.*, to_char(to_date(s_month,'YYYYMM'),'YYYY"0"Q') AS year_qtr FROM sales t ) t GROUP BY ites_name,year_qtr ORDER BY 1,2,3 ;
WITH t AS ( SELECT 'A' name, '201807' month, 50 quantity, 100 quantity_all FROM dual UNION ALL SELECT 'B', '201807', 50, 50 FROM dual UNION ALL SELECT 'B', '201808', 50, 10 FROM dual UNION ALL SELECT 'A', '201808', 50, 0 FROM dual UNION ALL SELECT 'A', '201809', 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 ;