예를들어 기간이 20180501~20200531 인경우
startdt enddt
20180501 20181231
20190101 20191231
20200101 20200531
이런식으로 데이터를 추출하고싶은데 쿼리가 쉽게 나오지 않네요 도움 부탁드립니다.
WITH t AS ( SELECT 1 pk, '20180501' sdt, '20200531' edt FROM dual ) SELECT pk , GREATEST(sdt, (SUBSTR(sdt, 1, 4) + lv - 1) || '0101') sdt , LEAST (edt, (SUBSTR(sdt, 1, 4) + lv - 1) || '1231') edt FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 20) WHERE lv <= SUBSTR(edt, 1, 4) - SUBSTR(sdt, 1, 4) + 1 ORDER BY pk, sdt ;