오라클 날짜관련 질문드립니다 0 6 1,208

by keonhwi [Oracle 기초] [2018.07.12 00:24:48]


  휴일
1 2018-07-03
2 2018-07-17

 

  시작일자 처리일자 마감일자
1 2018-07-02 7 2018-07-12
2 2018-07-05 7 2018-07-16
3 2018-07-11 14 2018-08-01

 

토,일과 휴일을 제외한 마감일자를 위의 표처럼 구하고 싶습니다

고수님들의 조언 부탁드립니다 ㅠㅠ

 

 

by 마농 [2018.07.12 09:04:09]
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
;

 


by 우리집아찌 [2018.07.12 09:30:45]

오라클도 재귀쿼리 되네요.. 처음 봤어요..


by 우리집아찌 [2018.07.12 09:23:37]
-- 일자로 계산했어요 박으로 계산하려면 -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  

 


by 우리집아찌 [2018.07.12 09:24:50]

아 마감일자 구하는거구낭.. ㅎㅎㅎ 

처리일자 구햇네요.. ㅡㅡ;


by 무존 [2018.07.12 10:06:01]

먼저 함수를 만들고

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;


by keonhwi [2018.07.12 13:40:28]

다들 감사합니다!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입