[Oracle] 시작일자~종료일자 기간내 모든 날짜(일자) 구하기

오라클에서 쿼리문을 작성하다 보면 테이블에 저장되지 않는 날짜까지 포함해서 조회해야 할 경우가 있다. 이럴 때는 시작일자와 종료일자를 설정하여 날짜 뷰를 생성하여 아우터 조인을 하면 쉽게 해결이 된다.

 

날짜 뷰 만들기

 SELECT TO_DATE('20211201','YYYYMMDD') + LEVEL - 1 AS dates
   FROM dual 
CONNECT BY LEVEL <= (TO_DATE('20211207','YYYYMMDD')-TO_DATE('20211201','YYYYMMDD')+1)

 

2012-12-01 ~ 2021-12-07 까지 날짜가 들어있는 뷰(View)를 동적으로 생성할 수 있다.

해당 날짜 뷰와 테이블을 아우터 조인을 하면 테이블에 존재하지 않는 날까까지 포함해서 조회할 수 있다.

 

활용 예제 - 출근 테이블에 없는 날짜 까지 조회

WITH date_range AS 
(
    SELECT TO_DATE('2021-12-01','YYYY-MM-DD') + LEVEL-1 dates
      FROM dual 
   CONNECT BY LEVEL <= (TO_DATE('2021-12-07','YYYY-MM-DD') - TO_DATE('2021-12-01','YYYY-MM-DD') + 1)
),
attendance AS 
(
    SELECT TO_DATE('2021-12-01', 'YYYY-MM-DD') AS attdate, '출근' AS attstat FROM dual UNION ALL
    SELECT TO_DATE('2021-12-02', 'YYYY-MM-DD') AS attdate, '출근' AS attstat FROM dual UNION ALL
    SELECT TO_DATE('2021-12-06', 'YYYY-MM-DD') AS attdate, '출근' AS attstat FROM dual
)
   
SELECT a.dates
     , b.attstat
  FROM date_range a
     , attendance b
 WHERE a.dates = b.attdate(+)
 ORDER BY a.dates