SELECT 1 class1 , 1 class2 ,MAX(DECODE( DOC_YM ,'201301' , TOTAL_CNT )) || ' ea' ,MAX(DECODE( DOC_YM ,TO_CHAR(ADD_MONTHS(TO_DATE('201301','YYYYMM'),1),'YYYYMM') , TOTAL_CNT )) || ' ea' ,MAX(DECODE( DOC_YM ,TO_CHAR(ADD_MONTHS(TO_DATE('201301','YYYYMM'),2),'YYYYMM') , TOTAL_CNT )) || ' ea' FROM ACCT_SUM UNION ALL SELECT 2 class1 , 1 class2 ,MAX(DECODE( DOC_YM ,'201301' , DECODE(TOTAL_CNT,NULL,0, ROUND(NORMAL_CNT / TOTAL_CNT * 100 )))) || ' %' ,MAX(DECODE( DOC_YM ,TO_CHAR(ADD_MONTHS(TO_DATE('201301','YYYYMM'),1),'YYYYMM'),DECODE(TOTAL_CNT,NULL,0, ROUND(NORMAL_CNT / TOTAL_CNT * 100 )))) || ' %' ,MAX(DECODE( DOC_YM ,TO_CHAR(ADD_MONTHS(TO_DATE('201301','YYYYMM'),2),'YYYYMM'),DECODE(TOTAL_CNT,NULL,0, ROUND(NORMAL_CNT / TOTAL_CNT * 100 )))) || ' %' FROM ACCT_SUM
WITH T(D1, D2, D4) AS ( SELECT '201301', TO_CHAR(ADD_MONTHS(TO_DATE('201301','YYYYMM'), 1), 'YYYYMM'), TO_CHAR(ADD_MONTHS(TO_DATE('201301','YYYYMM'), 1), 'YYYYMM') FROM DUAL ) SELECT 1 class1 , 1 class2 ,MAX(DECODE( DOC_YM ,D1 , TOTAL_CNT )) || ' ea' ,MAX(DECODE( DOC_YM ,D2 , TOTAL_CNT )) || ' ea' ,MAX(DECODE( DOC_YM ,D3 , TOTAL_CNT )) || ' ea' FROM ACCT_SUM, T UNION ALL SELECT 2 class1 , 1 class2 ,MAX(DECODE( DOC_YM ,D1,DECODE(TOTAL_CNT,NULL,0, ROUND(NORMAL_CNT / TOTAL_CNT * 100 )))) || ' %' ,MAX(DECODE( DOC_YM ,D2,DECODE(TOTAL_CNT,NULL,0, ROUND(NORMAL_CNT / TOTAL_CNT * 100 )))) || ' %' ,MAX(DECODE( DOC_YM ,D3,DECODE(TOTAL_CNT,NULL,0, ROUND(NORMAL_CNT / TOTAL_CNT * 100 )))) || ' %' FROM ACCT_SUM, T --안스마트, 후다닥 ==3==3
SELECT class1, class2 , MIN(DECODE(m, 0, v)) v1 , MIN(DECODE(m, 1, v)) v2 , MIN(DECODE(m, 2, v)) v3 FROM ( SELECT class1, class2 , MONTHS_BETWEEN(TO_DATE(doc_ym, 'yyyymm'), TO_DATE(:v_ym, 'yyyymm')) m -- 24개 기준별 계산공식 나열 , DECODE(class1 || '-' || class2 , '1-1', total_cnt || ' ea' , '2-1', NVL(ROUND(normal_cnt / total_cnt * 100), 0) || ' %' ) v FROM acct_sum , (-- 24개 기준 -- SELECT 1 class1, 1 class2 FROM dual UNION ALL SELECT 2, 1 FROM dual ) WHERE doc_ym >= :v_ym AND doc_ym <= TO_CHAR(ADD_MONTHS(TO_DATE(:v_ym, 'yyyymm'), 2), 'yyyymm') ) GROUP BY class1, class2 ORDER BY class1, class2 ;