[오라클] 반기 구하는 쿼리

참고 : http://www.gurubee.net/article/62773

-------------------- 5년치 반기 -------------------------

SELECT LV

     , TO_CHAR(YEAR - CEIL((LV-BANGI)/2)) YEAR

     , TO_CHAR(2 - MOD(LV-BANGI+1, 2)) BAN

  FROM (SELECT '2017' YEAR, '2' BANGI FROM DUAL)

     , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10)

---------------- 반기 활용한 5년간 코드 사용수 ------------------

 WITH EXTRC_DT AS ( SELECT LV

                         , TO_CHAR(SEL_YEAR - CEIL((LV-BANGI)/2)) AS YY

                         , TO_CHAR(2 - MOD(LV-BANGI+1, 2)) BAN

                      FROM (SELECT '2017' SEL_YEAR, CASE WHEN '2017'=TO_CHAR(SYSDATE, 'YYYY') THEN CEIL(TO_CHAR(SYSDATE, 'q')/2) ELSE 2 END AS BANGI FROM DUAL)

                         , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10) )                       

    SELECT T1.CODE_ID

         , T1.CODE_NM

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-5 AND T2.BAN = '1' THEN T3.CNT END), 0) AS YH_01

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-5 AND T2.BAN = '2' THEN T3.CNT END), 0) AS YH_02

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-4 AND T2.BAN = '1' THEN T3.CNT END), 0) AS YH_03

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-4 AND T2.BAN = '2' THEN T3.CNT END), 0) AS YH_04

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-3 AND T2.BAN = '1' THEN T3.CNT END), 0) AS YH_05

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-3 AND T2.BAN = '2' THEN T3.CNT END), 0) AS YH_06

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-2 AND T2.BAN = '1' THEN T3.CNT END), 0) AS YH_07

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-2 AND T2.BAN = '2' THEN T3.CNT END), 0) AS YH_08

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-1 AND T2.BAN = '1' THEN T3.CNT END), 0) AS YH_09

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017')-1 AND T2.BAN = '2' THEN T3.CNT END), 0) AS YH_10

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017') AND T2.BAN = '1' THEN T3.CNT END), 0) AS YH_11

         , NVL(MAX(CASE WHEN T2.YY = TO_NUMBER('2017') AND T2.BAN = '2' THEN T3.CNT END), 0) AS YH_12

         , NVL(SUM(T3.CNT), 0) AS TOT_SUM

      FROM TB_CO_CODE T1

     LEFT OUTER JOIN EXTRC_DT T2

        ON 1 = 1      

     LEFT OUTER JOIN ( SELECT DISTINCT SS.PNTT_TY_CD_ID, SS.RCEPT_YY, SS.RCEPT_HF, COUNT(*) OVER(PARTITION BY SS.RCEPT_YY, SS.RCEPT_HF, SS.PNTT_TY_CD_ID) AS CNT

                         FROM ( SELECT DISTINCT CVP_ID, SUBSTR(A.RCEPT_DT, 1, 4) AS RCEPT_YY, CEIL(TO_CHAR(TO_DATE(SUBSTR(A.RCEPT_DT, 1, 6), 'YYYYMM'), 'q')/2) AS RCEPT_HF

                                     , REGEXP_SUBSTR(A.PNTT_TY_CD_ID, '[^,]+', 1, LEVEL) AS PNTT_TY_CD_ID

                                  FROM TB_AU_CVP A

                               CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(A.PNTT_TY_CD_ID, '[^,]+',''))+1 ) SS

                        WHERE TO_NUMBER(SS.RCEPT_YY) BETWEEN TO_NUMBER('2017')-5 AND TO_NUMBER('2017')  ) T3

        ON T1.CODE_ID = T3.PNTT_TY_CD_ID

       AND T2.YY = T3.RCEPT_YY

       AND T2.BAN = T3.RCEPT_HF

     WHERE T1.GROUP_ID = 'AUD_048'

     GROUP BY T1.CODE_ID, T1.CODE_NM

     ORDER BY T1.CODE_ID ASC;