1.
param => ‘202107’ SELECT TO_CHAR(ADD_MONTHS(TO_DATE(‘202107’, ‘YYYYMM’),1), ‘YYYYMMDD’ )
FROM DUAL
결과값 => 20210801
2.
param => ‘202107’
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(‘202107’, ‘YYYYMM’),-3), ‘YYYYMMDD’ )
FROM DUAL
결과값 => 20210401
3.
param => ‘202107’
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(‘202107’, ‘YYYYMM’), LEVEL-4), ‘YYYYMM’ )
FROM DUAL
CONNECT BY LEVEL <= 4
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(‘202107’, ‘YYYYMM’), LEVEL-4), ‘YYYYMM’ )
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(‘202107’, ‘YYYYMM’), ADD_MONTHS(TO_DATE(‘202107’, ‘YYYYMM’), -4))
결과값 => 202104
202105
202106
202107
3.
SELECT A.V_DATE, B.IDX_CD, C.TOT_RESULT
FROM (
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(‘202107’, ‘YYYYMM’), LEVEL-4), ‘YYYYMM’ ) AS V_DATE
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(‘202107’, ‘YYYYMM’), ADD_MONTHS(TO_DATE(‘202107’, ‘YYYYMM’), -4))
) A
LEFT OUTER JOIN (
SELECT LEVEL AS IDX_CD
FROM DUAL
CONNECT BY LEVEL < 4
) B ON 1=1
LEFT OUTER JOIN (
/*TEST TABLE 이 존재한다고 가정*/
SELECT V_DATE, IDX_CD, SUM(RESULT) AS TOT_RESULT
FROM TEST
GROUP BY V_DATE, IDX_CD
) C ON A.V_DATE = C.V_DATE
AND B.IDX_CD = C.IDX_CD
결과값 =>
V_DATE IDX_CD TOT_RESULT
202104 1 77
202104 2 25
202104 3 85
202105 1 99
202105 2 17
202105 3 23
202106 1 22
202106 2 88
202106 3 51
202107 1 59
202107 2 94
202107 3 39