[Oracle] 이전/다음 영업일(날짜) 구하기

출처 : http://blog.daum.net/coschao/16
         http://database.sarang.net/?inc=read&aid=38441&criteria=oracle&subcrit=&id=&limit=20&keyword=%C8%DE%C0%CF&page=1



1. 입력일 포함 다음 영업일 구하기 (휴일 테이블 사용)

휴일정보테이블(HOLIDAY_INFO_TABLE)은
    HOLIDAY_YMD   VARCHAR2(8 BYTE)  NOT NULL, /* 휴일날짜(yyyyMMdd) */
    HOLIDAY_NM    VARCHAR2(30 BYTE) NOT NULL, /* 휴일명 */

    .... 기타 추가정보

로 구성되어 있다고 가정됨.

 

아래에서 IN_YMD 는 'yyyyMMdd' 형식의 입력되는 변수(펑션의 변수 처럼..)

------------------------

    select b.YMD /* into RT_VALUE -- (펑션이라면 반환값) */
      from (
            select a.YMD, s.HOLIDAY_NM || case TO_CHAR(TO_DATE(a.YMD, 'YYYYMMDD'), 'D')
                                          when '1' then '일'
                                          when '7' then '토'
                                          else '' end as HOLI_DOW_TXT
              from (
                        select to_char(x.YMD_DATE - 1 + level, 'YYYYMMDD') as YMD
                          from (select to_date(IN_YMD, 'YYYYMMDD') as YMD_DATE from dual) x
                         where (x.YMD_DATE - 1 + level) <= last_day(x.YMD_DATE)
                       connect by level<=15 /* 입력일 이후 15일간 줄 세워봄. 15일간 연짱 휴일일리는 없겠지... */
                 ) a, HOLIDAY_INFO_TABLE s
             where a.YMD = s.HOLIDAY_YMD(+)
             order by a.YMD
        ) b
    where b.HOLI_DOW_TXT is null
      and rownum = 1

2. 두 기간 사이의 영업일 건수 구하기

WITH off_day AS
(
SELECT '20110301' dt, '삼일절' cmt FROM dual
)
SELECT COUNT(*) cnt
  FROM (SELECT TO_CHAR(sdt + LEVEL - 1, 'yyyymmdd') dt
             , TO_CHAR(sdt + LEVEL - 1, 'd') d
          FROM (SELECT TO_DATE('20110311', 'yyyymmdd') sdt
                     , TO_DATE('20110315', 'yyyymmdd') edt
                  FROM dual)
        CONNECT BY LEVEL <= edt - sdt + 1
        ) a
     , off_day b
 WHERE a.dt = b.dt(+)
   AND a.d NOT IN ('1', '7')
   AND b.dt IS NULL




3. 도시별 휴일 제외한 영업일 구하기 

메인테이블 일자 영업일 도시코드 20120321 2 01 20120322 5 02 20120323 4 03 휴일테이블 도시코드 휴일일자 01 20120322 01 20120323 02 20120323 03 20120326 메인테이블의 일자에 영업일을 더해서 다음 영업일자를 구해야 합니다. 다음 영업일은 토/일은 기본으로 제외시키고 휴일테이블의 해당 도시에 해당하는 휴일도 제외하여 계산되어야 하는데요. 예를 들면 20120321 +2 영업일은 22,23(휴일), 24,25(토,일) 이므로 20120327일이 나와야 합니다. Function등을 사용하지 않고 쿼리로만 사용할 수 있는 방법이 있을까요? 오라클 11G 기준 재귀쿼리입니다. 이하 버전에서는 안돌아 갑니다. WITH t1 AS ( SELECT '20120321' dt, 2 dy, '01' ct FROM dual UNION ALL SELECT '20120322', 5, '02' FROM dual UNION ALL SELECT '20120323', 4, '03' FROM dual ) , t2 AS ( SELECT '01' ct, '20120322' dt FROM dual UNION ALL SELECT '01', '20120323' FROM dual UNION ALL SELECT '02', '20120323' FROM dual UNION ALL SELECT '03', '20120326' FROM dual ) , t3(dt, dy, ct) AS ( SELECT * FROM t1 UNION ALL SELECT TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'yyyymmdd') dt , a.dy - CASE WHEN b.dt IS NOT NULL THEN 0 WHEN TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'd') IN ('1','7') THEN 0 ELSE 1 END AS dy , a.ct FROM t3 a LEFT JOIN t2 b ON a.ct = b.ct AND b.dt = TO_CHAR(TO_DATE(a.dt, 'yyyymmdd') + 1, 'yyyymmdd') WHERE a.dy > 0 ) SELECT ct, dt FROM t3 WHERE dy = 0 ORDER BY ct, dt ;