求一个给定一个日期和完工期限的天数，推算出具体完工日期的函数，要求排除所有节假日。

Question

求一个给定一个日期和完工期限的天数，推算出具体完工日期的函数，要求排除所有节假日。
设想：建立一个节假日表。记录所有重大的假期安排。及调修的具体日期。2011年全年公休假放假安排_中国政府网
节日 放假时间 放假天数 调休上班日期  
元旦 1月1日至3日放假 共3天 无调休  
春节 2月2日至8日放假调休 共7天 1月30日、2月12日（星期日、六）  
清明节 4月3日至5日放假调休 共3天 4月2日（星期六）  
劳动节 4月30日至5月2日放假 共3天 无调休  
端午节 6月4日至6日放假 共3天 无调休  
中秋节 9月10日至12日放假 共3天 无调休  
国庆节 10月1日至7日放假调休 共7天 10月8日、9日（星期六、日）  例子：录入的时间是2月1日8点，工单要求完工的时限为：2天，那么要求输入的完工具体时间为：2011-02-09，既有效工作日为1号和9号2天。如果工单要求完工的时限为：5天，那么要求输出的完工具体时间为：2011-02-12，既有效工作日为1，9，10,11,12 5天。其中 2月12日星期六，但它是调休的，以有效工作日计算。
求一个能这样输出 具体完工时间的函数，谢谢！

Answer 1

--cal(t_date,flag)：节假日表，t_date:日期,flag(0:节假日,1:补休工作日，如4月2日)
--2011-01-01：日历开始日期，2012-01-01：日历结束日期(根据需要调整)
--start_date：指定任务开始时间，n_days：完工天数
WITH t AS
 (SELECT DATE '2011-01-01' + LEVEL - 1 a_date
    FROM dual
  CONNECT BY LEVEL <= DATE '2012-01-01' - DATE '2011-01-01')
SELECT MAX(a_date) end_date
  FROM (SELECT *
          FROM (SELECT a_date
                  FROM (SELECT a_date
                          FROM t
                         WHERE to_char(a_date, 'd') NOT IN ('1', '7'))
                 WHERE a_date NOT IN
                       (SELECT t_date FROM cal WHERE flag = '0')
                UNION
                SELECT t_date FROM cal WHERE flag = '1')
         WHERE a_date >= date'&start_date'
           AND rownum <= &n_days)

Answer 2

--写了个函数。顺便学习下楼上的想法。
create table holidays(
  serialno     varchar2(10), --流水号
  startdate    date,         --放假开始时间
  enddate      date,         --放假结束时间
  days         number,       --放假时长
  needworkflag varchar2(1),  --是否需要调休 1:需要 0 不需要
  isoverdue    varchar2(1)   --是否过期失效 1:过期 0 没过期
);create table needwork(
  serialno     varchar2(10), --流水号,与放假表关联
  workiterm    varchar2(10), --倒休序号
  workdate     date          --倒休日期
);
alter table holidays add constraints pk_serialno primary key(serialno);
alter table needwork add constraints pk_serialno_workiterm primary key(serialno,workiterm);
alter table needwork add constraints fk_serialno foreign key(serialno) references holidays(serialno);
insert into holidays
select '2011001',date'2011-01-01',date'2011-01-03',3,0,0 from dual union all
select '2011002',date'2011-02-02',date'2011-02-08',7,1,0 from dual union all
select '2011003',date'2011-04-03',date'2011-04-05',3,1,0 from dual union all
select '2011004',date'2011-04-30',date'2011-05-02',3,0,0 from dual union all
select '2011005',date'2011-06-04',date'2011-06-06',3,0,0 from dual union all
select '2011006',date'2011-09-10',date'2011-09-12',3,0,0 from dual union all
select '2011007',date'2011-10-01',date'2011-10-07',7,1,0 from dual;insert into needwork
select '2011002','1',date'2011-01-30' from dual union all
select '2011002','2',date'2011-02-12' from dual union all
select '2011003','1',date'2011-04-02' from dual union all
select '2011007','1',date'2011-10-08' from dual union all
select '2011007','2',date'2011-10-09' from dual;
CREATE OR REPLACE FUNCTION IS_HOLIDAYS(
  I_STARTDATE   DATE
) RETURN VARCHAR2 IS
  V_WEEKDAY      NUMBER;
  V_CNT_HOLIDAYS NUMBER;
  V_CNT_NEEDWORK NUMBER;
BEGIN
  --判断输入日期是否为法定假日
  SELECT COUNT(1)
    INTO V_CNT_HOLIDAYS
    FROM HOLIDAYS
   WHERE I_STARTDATE BETWEEN STARTDATE AND ENDDATE
     AND ISOVERDUE = 0;
  IF V_CNT_HOLIDAYS > 0 THEN
    RETURN 1;
  ELSE
    --判断输入日期是否为周末,并且是不是倒休的周末
    SELECT TO_CHAR(I_STARTDATE, 'd', 'nls_date_language=american')
      INTO V_WEEKDAY
      FROM DUAL;    SELECT COUNT(1)
      INTO V_CNT_NEEDWORK
      FROM NEEDWORK T1,HOLIDAYS T2
     WHERE T1.WORKDATE = TRUNC(I_STARTDATE)
       AND T2.ISOVERDUE = 0
       AND T2.SERIALNO = T1.SERIALNO;    IF V_WEEKDAY IN (1, 7) AND V_CNT_NEEDWORK = 0 THEN
      RETURN 2;
    END IF;
  END IF;
  --正常
  RETURN 0;EXCEPTION
  WHEN OTHERS THEN
   RETURN 1;
END;
CREATE OR REPLACE FUNCTION WORK_DAYS(
  I_STARTDATE   DATE,
  I_INTERVALDAY NUMBER
) RETURN VARCHAR2 IS
  V_FLAG       VARCHAR2(1);
  V_LOOP       NUMBER := 1;
  V_STARTDATE  DATE   := I_STARTDATE;
  V_RETURNDATE VARCHAR2(20);
BEGIN  V_FLAG := IS_HOLIDAYS(I_STARTDATE);  IF V_FLAG = 1 THEN
    RETURN '输入的时间不能为法定假日！';
  ELSIF V_FLAG = 2 THEN
    RETURN '输入的时间不能为周末！';
  ELSIF I_INTERVALDAY = 0 THEN
    RETURN '输入的时间范围不能为零！';
  END IF;  --求结果
  WHILE( V_LOOP <= I_INTERVALDAY )
  LOOP
    V_STARTDATE := V_STARTDATE + 1;
    V_FLAG := IS_HOLIDAYS(V_STARTDATE);    IF V_FLAG = 0 THEN
      V_RETURNDATE := TO_CHAR(V_STARTDATE,'yyyy-mm-dd hh24:mi:ss');
      V_LOOP := V_LOOP + 1;
    ELSE
      NULL;
    END IF;
  END LOOP;
  
  RETURN V_RETURNDATE;EXCEPTION
  WHEN OTHERS THEN
   RETURN '程序有异常！';
END;

Answer 3

--测试结果
[TEST@orcl] SQL>select WORK_DAYS(date'2011-07-01',6) from dual;WORK_DAYS(DATE'2011-07-01',6)
--------------------------------------------------------------------------------
2011-07-11 00:00:00

Answer 4

WITH T AS
 (SELECT TO_DATE('2011-01-01', 'yyyy-mm-dd') + ROWNUM - 1 AS DAY
    FROM DUAL
  CONNECT BY LEVEL <= DATE '2012-01-01' - DATE '2011-01-01')
SELECT DAY
  FROM (SELECT DAY, ROW_NUMBER() OVER(ORDER BY DAY) RN
          FROM (SELECT DAY,
                       (CASE
                         WHEN DAY BETWEEN TO_DATE('2011-01-01', 'yyyy-mm-dd') AND
                              TO_DATE('2011-01-03', 'yyyy-mm-dd') THEN
                          0  -- 元旦 1月1日至3日放假 共3天 无调休
                         WHEN DAY BETWEEN TO_DATE('2011-02-02', 'yyyy-mm-dd') AND
                              TO_DATE('2011-02-08', 'yyyy-mm-dd') THEN
                          0 -- 春节 2月2日至8日放假调休 共7天
                         WHEN DAY BETWEEN TO_DATE('2011-04-03', 'yyyy-mm-dd') AND
                              TO_DATE('2011-04-05', 'yyyy-mm-dd') THEN
                          0 -- 清明节 4月3日至5日放假调休 共3天
                       
                         ELSE
                          1
                       END) NEEDWORKFLAG
                  FROM T)
         WHERE NEEDWORKFLAG <> 0
           AND DAY >= TO_DATE('2011-02-01', 'yyyy-mm-dd'))
 WHERE RN = 5