--머리가 안 굴러가서 복잡하네요; select greatest(:sdt,fdt) fdt, least(to_char(sysdate,'yyyymmdd'), tdt) tdt from ( select :sdt,yyyy||'0301' fdt , to_char(last_day(to_date(to_char(yyyy + 1)||'0201','yyyymmdd')),'yyyymmdd') tdt from ( select to_number(substr(:sdt,1,4)) + level - 1 yyyy from dual connect by level <= to_number(to_char(sysdate,'yyyy')) - to_number(substr(:sdt,1,4)) + case when to_char(sysdate,'yyyy')||'0301' > to_char(sysdate,'yyyymmdd') then 0 else 1 end ) )
SELECT MIN(dt) entn_dt, MAX(dt) end_dt FROM ( SELECT dt, CASE WHEN TO_CHAR(dt, 'MM') < '03' THEN TO_CHAR(dt, 'YYYY') ELSE TO_CHAR(TO_NUMBER(TO_CHAR(dt, 'YYYY')) + 1) END year FROM ( SELECT TO_DATE('20120602', 'yyyymmdd') + LEVEL - 1 dt FROM dual CONNECT BY LEVEL <= SYSDATE - TO_DATE('20120602', 'yyyymmdd') + 1 ) ) GROUP BY year ORDER BY year
SELECT GREATEST(ST_DATE, ST),
LEAST(END_DATE, DT)
FROM ( SELECT TO_DATE(TO_CHAR(TO_NUMBER(SUBSTR(TT, 1, 4)) + LEVEL - 2) || '0301', 'YYYYMMDD') AS ST_DATE,
LAST_DAY(TO_DATE(TO_CHAR(TO_NUMBER(SUBSTR(TT, 1, 4)) + LEVEL - 1) || '02', 'YYYYMM')) AS END_DATE,
TT, ST, DT
FROM DUAL,
( SELECT '20130124' AS TT, TO_DATE('20130124', 'YYYYMMDD') AS ST, TRUNC(SYSDATE, 'DD') AS DT FROM DUAL)
CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - TO_NUMBER(SUBSTR(TT, 1, 4)) + 2 )
WHERE END_DATE > ST
현재월 기준으로 구간을 나눈다고 생각하고 쿼리 작성했는데.
위 답변을 보니 3월을 기준으로 나누는 것일 수도 있겠네요.
3월 기준으로도 풀어봤습니다.
SELECT TO_CHAR(GREATEST(sdt , ADD_MONTHS(edt, -12*(LEVEL-1)) ), 'yyyymmdd') sdt , TO_CHAR(LEAST(sysdate, ADD_MONTHS(edt, -12*(LEVEL-2))-1), 'yyyymmdd') edt FROM (SELECT TO_DATE('20120602', 'yyyymmdd') sdt , TRUNC(sysdate, 'mm') edt -- 현재월 기준 -- , ADD_MONTHS(TRUNC(ADD_MONTHS(TRUNC(sysdate, 'mm'), -2), 'yy'), 2) edt -- 3월 기준 FROM dual) CONNECT BY LEVEL <= CEIL(MONTHS_BETWEEN(edt, sdt)/12) + 1 ORDER BY sdt ;