WITH holiday AS ( SELECT 1 id, '2018-07-03' dt FROM dual UNION ALL SELECT 2, '2018-07-17' FROM dual ) , request AS ( SELECT 1 id, '2018-07-02' reg_dt, 7 work_day FROM dual UNION ALL SELECT 2, '2018-07-05', 7 FROM dual UNION ALL SELECT 3, '2018-07-11', 14 FROM dual ) -- 1. 행복제 방법 -- SELECT id, reg_dt, work_day , TO_CHAR(TO_DATE(reg_dt, 'yyyy-mm-dd') + lv, 'yyyy-mm-dd') limit_dt FROM (SELECT a.id, a.reg_dt, a.work_day , b.lv , c.dt , ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY b.lv) rn FROM request a CROSS JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) b LEFT OUTER JOIN holiday c ON TO_CHAR(TO_DATE(a.reg_dt, 'yyyy-mm-dd') + b.lv, 'yyyy-mm-dd') = c.dt WHERE c.dt IS NULL AND TO_CHAR(TO_DATE(a.reg_dt, 'yyyy-mm-dd') + b.lv, 'd') NOT IN ('1', '7') ) WHERE work_day = rn ;
WITH holiday AS ( SELECT 1 id, '2018-07-03' dt FROM dual UNION ALL SELECT 2, '2018-07-17' FROM dual ) , request AS ( SELECT 1 id, '2018-07-02' reg_dt, 7 work_day FROM dual UNION ALL SELECT 2, '2018-07-05', 7 FROM dual UNION ALL SELECT 3, '2018-07-11', 14 FROM dual ) -- 2. Recursive WITH , tmp(id, reg_dt, work_day, lv, cnt) AS ( SELECT a.id, a.reg_dt, a.work_day , 0 lv , 0 cnt FROM request a UNION ALL SELECT a.id, a.reg_dt, a.work_day , a.lv + 1 lv , a.cnt + CASE WHEN b.dt IS NOT NULL OR TO_CHAR(TO_DATE(a.reg_dt, 'yyyy-mm-dd') + a.lv + 1, 'd') IN ('1','7') THEN 0 ELSE 1 END cnt FROM tmp a LEFT OUTER JOIN holiday b ON TO_CHAR(TO_DATE(a.reg_dt, 'yyyy-mm-dd') + a.lv + 1, 'yyyy-mm-dd') = b.dt WHERE a.cnt < a.work_day ) SELECT id, reg_dt, work_day , TO_CHAR(TO_DATE(reg_dt, 'yyyy-mm-dd') + lv, 'yyyy-mm-dd') limit_dt FROM tmp WHERE cnt = work_day ;
-- 일자로 계산했어요 박으로 계산하려면 -1 하셔야할듯합니다. WITH HOLIDAY AS ( SELECT 1 SEQ , TO_DATE('2018-07-03','YYYY-MM-DD') DT FROM DUAL UNION ALL SELECT 2 , TO_DATE('2018-07-17','YYYY-MM-DD') FROM DUAL ) , T AS ( SELECT 1 SEQ , TO_DATE('2018-07-02','YYYY-MM-DD') ST_DT , TO_DATE('2018-07-12','YYYY-MM-DD') ED_DT FROM DUAL UNION ALL SELECT 2 , TO_DATE('2018-07-05','YYYY-MM-DD') ST_DT , TO_DATE('2018-07-16','YYYY-MM-DD') ED_DT FROM DUAL UNION ALL SELECT 3 , TO_DATE('2018-07-11','YYYY-MM-DD') ST_DT , TO_DATE('2018-08-01','YYYY-MM-DD') ED_DT FROM DUAL ) SELECT Z.SEQ , Z.ST_DT , Z.ED_DT , COUNT(*) "처리일자" FROM (SELECT A.SEQ , ST_DT , ED_DT , ST_DT + LV - 1 AS DD FROM T A , (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 30 ) B WHERE B.LV < ( A.ED_DT - A.ST_DT + 1 ) + 1 ORDER BY A.SEQ , 4 ) Z WHERE NOT EXISTS ( SELECT 'X' FROM HOLIDAY H WHERE H.DT = Z.DD ) AND TO_CHAR(Z.DD,'D') NOT IN ( 7 , 1 ) GROUP BY Z.SEQ , Z.ST_DT , Z.ED_DT
먼저 함수를 만들고
CREATE OR REPLACE function f_get_subdays (
pSTR IN VARCHAR2
, pDAYS IN VARCHAR2
)
return varchar2
is
ret_val varchar2(10) := '';
vDATE varchar2(10) := '';
vDAYS number := 0 ;
begin
FOR I IN 0..999999 LOOP
if to_char(to_date(pSTR,'YYYYMMDD') + I,'d') not in ('1','7') then
vDAYS := vDAYS + 1;
vDATE := to_char(to_date(pSTR,'YYYYMMDD') + I,'YYYYMMDD');
end if;
if vDAYS >= to_number(pDAYS) then
ret_val := vDATE;
goto RTN;
end if;
END LOOP;
<<RTN>>
return ret_val;
exception
when no_data_found then
return 0 ;
end ;
/
이런형식으로 call 하면 될거 같습니다.
select f_get_subday('20180701','11') from dual;