-- *(01) 创建节假日表 create table holidays (hdate date);-------------------- 添加节假日(添加近三年的节假日,楼主如果需要更多的节假日,自己去添加好啦) -------------------- -- 2009年节假日 -- 元旦 insert into holidays(hdate) values(to_date('2009-01-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-01-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-01-03','yyyy-mm-dd'));-- 春节 insert into holidays(hdate) values(to_date('2009-01-25','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-01-26','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-01-27','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-01-28','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-01-29','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-01-30','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-01-31','yyyy-mm-dd'));-- 清明节 insert into holidays(hdate) values(to_date('2009-04-04','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-04-05','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-04-06','yyyy-mm-dd'));-- 劳动节 insert into holidays(hdate) values(to_date('2009-05-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-05-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-05-03','yyyy-mm-dd'));-- 端午节 insert into holidays(hdate) values(to_date('2009-05-28','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-05-29','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-05-30','yyyy-mm-dd'));-- 国庆节、中秋节 insert into holidays(hdate) values(to_date('2009-10-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-10-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-10-03','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-10-04','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-10-05','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-10-06','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-10-07','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2009-10-08','yyyy-mm-dd'));---------------------------------------------------------------- -- 2010年节假日 -- 元旦 insert into holidays(hdate) values(to_date('2010-01-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-01-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-01-03','yyyy-mm-dd'));-- 春节 insert into holidays(hdate) values(to_date('2010-02-13','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-02-14','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-02-15','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-02-16','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-02-17','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-02-18','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-02-19','yyyy-mm-dd'));-- 清明节 insert into holidays(hdate) values(to_date('2010-04-03','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-04-04','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-04-05','yyyy-mm-dd'));-- 劳动节 insert into holidays(hdate) values(to_date('2010-05-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-05-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-05-03','yyyy-mm-dd'));-- 端午节 insert into holidays(hdate) values(to_date('2010-06-14','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-06-15','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-06-16','yyyy-mm-dd'));-- 中秋节 insert into holidays(hdate) values(to_date('2010-09-22','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-09-23','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-09-24','yyyy-mm-dd'));-- 国庆节 insert into holidays(hdate) values(to_date('2010-10-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-10-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-10-03','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-10-04','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-10-05','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-10-06','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2010-10-07','yyyy-mm-dd'));---------------------------------------------------------------- -- 2011年节假日-- 元旦 insert into holidays(hdate) values(to_date('2011-01-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-01-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-01-03','yyyy-mm-dd'));-- 春节 insert into holidays(hdate) values(to_date('2011-02-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-02-03','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-02-04','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-02-05','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-02-06','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-02-07','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-02-08','yyyy-mm-dd'));-- 清明节 insert into holidays(hdate) values(to_date('2011-04-03','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-04-04','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-04-05','yyyy-mm-dd'));-- 劳动节 insert into holidays(hdate) values(to_date('2011-04-30','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-05-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-05-02','yyyy-mm-dd'));-- 端午节 insert into holidays(hdate) values(to_date('2011-06-04','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-06-05','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-06-06','yyyy-mm-dd'));-- 中秋节 insert into holidays(hdate) values(to_date('2011-09-10','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-09-11','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-09-12','yyyy-mm-dd'));-- 国庆节 insert into holidays(hdate) values(to_date('2011-10-01','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-10-02','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-10-03','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-10-04','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-10-05','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-10-06','yyyy-mm-dd')); insert into holidays(hdate) values(to_date('2011-10-07','yyyy-mm-dd'));
-- (02) 创建函数:------------ create or replace function wk_hours_between( i_startTime varchar2, -- 起始时间:( 格式:'YYYY-MM-DD HH24:MI:SS' ) i_endTime varchar2 -- 结束时间:( 格式:'YYYY-MM-DD HH24:MI:SS' ) ) return number is v_real_startTime date; v_real_endTime date; v_hours number(18,0); v_number number(18,4); begin v_real_startTime := to_date(i_startTime,'YYYY-MM-DD HH24:MI:SS'); v_real_endTime := to_date(i_endTime,'YYYY-MM-DD HH24:MI:SS'); if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间,将起始时间与结束时间替换 then select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime from dual; end if; if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间,将起始时间与结束时间替换 then select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime from dual; end if; if v_real_startTime<trunc(v_real_startTime,'dd')+8/24 -- 如果小于当天8点,将其置为当天8天(因为你是8点上班) then v_real_startTime:=trunc(v_real_startTime,'dd')+8/24; -- 如果大于当天12点,且小于当天14点,将其置为当天14点(因为你下午是14点上班) elsif v_real_startTime>trunc(v_real_startTime,'dd')+12/24 and v_real_startTime<trunc(v_real_startTime,'dd')+14/24 then v_real_startTime:=trunc(v_real_startTime,'dd')+14/24; -- 如果大于当天18点,将其置为第二天8天 elsif v_real_startTime>trunc(v_real_startTime,'dd')+18/24 then v_real_startTime:=trunc(v_real_startTime+1,'dd')+8/24; end if; -- 如果小于当天8点,将其置为昨天18点 if v_real_endTime<trunc(v_real_endTime,'dd')+8/24 then v_real_endTime:=trunc(v_real_endTime-1,'dd')+18/24; -- 如果大于当天12点,且小于当天14点,将其置为当天12点(因为你上午是12点下班) elsif v_real_endTime>trunc(v_real_endTime,'dd')+12/24 and v_real_endTime<trunc(v_real_endTime,'dd')+14/24 then v_real_endTime:=trunc(v_real_endTime,'dd')+12/24; elsif v_real_endTime>trunc(v_real_endTime,'dd')+18/24 -- 如果大于当天18点,将其置为当天18天(因为你是18点下班) then v_real_endTime:=trunc(v_real_endTime,'dd')+18/24; end if; with a as( select v_real_startTime+(level-1)/24 as cdate, trunc(v_real_startTime+(level-1)/24,'hh') as tr_cdate from dual connect by level <= (v_real_endTime-v_real_startTime)*24+2 ), b as( select cdate, tr_cdate cdate2 from a where trunc(a.cdate) not in (select hdate from holidays) and to_char(a.cdate,'D') not in ('1','7') ), -- 排除周六、日 和 节假日 c as( select (case when to_char(t1.cdate,'hh24') in ('12','18') then trunc(t1.cdate,'hh') else t1.cdate end) as cdate1, (case when to_char(t2.cdate,'hh24') in ('14') then trunc(t2.cdate,'hh') else t2.cdate end) as cdate2 from b t1 left join b t2 on trunc(t1.cdate,'hh24')=trunc(t2.cdate+1/24,'hh24') order by (case when to_char(t1.cdate,'hh') in ('08','12','14','18') then trunc(t1.cdate,'hh') else t1.cdate end) ), d as ( select (case when c.cdate1>v_real_endTime then v_real_endTime else c.cdate1 end) as cdate1, c.cdate2 from c where to_char(c.cdate1,'hh24') in ('09','10','11','12','15','16','17','18') ) select sum(d.cdate1-nvl(d.cdate2,d.cdate1))*24 into v_number from d; return v_number; end; /-- *(03) 测试: set serveroutput on; select wk_hours_between('2011-03-21 08:00:00','2011-03-21 12:00:00') from dual;
法定假日表和工作时间表都是需要的。 create table 工作时间 (开始日期 date, 结束日期 date, 小时 number(2)); insert into 工作时间 values (to_date('2000-01-01','yyyy-MM-dd'), to_date('2010-12-31','yyyy-MM-dd'), 8); insert into 工作时间 values (to_date('2011-01-01','yyyy-MM-dd'), to_date('2020-12-31','yyyy-MM-dd'), 7);create table 法定假日(日期 date); insert into 法定假日 values ...;with 剩余日子 as ( select to_date('2011-03-21', 'yyyy-mm-dd') + rownum as 日期 from dual connect by to_date('2011-03-21', 'yyyy-mm-dd') + rownum < to_date('2012-01-01', 'yyyy-mm-dd')
) select sum(工作时间.小时) as "还需要工作小时", count(剩余日子.日期) as "还需要工作天数" from 剩余日子 inner join 工作时间 on 剩余日子.日期 between 工作时间.开始日期 and 工作时间.结束日期 left join 法定假日 on 剩余日子.日期 = 法定假日.日期 where 法定假日.日期 is null and to_char(剩余日子.日期, 'D') between '2' and '6' ; 还需要工作小时 还需要工作天数 1358 194
-- 最后可以加个nvl函数,修改如下: select nvl(sum(d.cdate1-nvl(d.cdate2,d.cdate1))*24,0) into v_number from d;
参考下这个帖子
http://topic.csdn.net/u/20100907/16/4ad79e71-187b-41d0-bfe5-e2cdac09cc02.html?71403
create table holidays
(hdate date);-------------------- 添加节假日(添加近三年的节假日,楼主如果需要更多的节假日,自己去添加好啦) --------------------
-- 2009年节假日
-- 元旦
insert into holidays(hdate)
values(to_date('2009-01-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-01-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-01-03','yyyy-mm-dd'));-- 春节
insert into holidays(hdate)
values(to_date('2009-01-25','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-01-26','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-01-27','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-01-28','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-01-29','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-01-30','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-01-31','yyyy-mm-dd'));-- 清明节
insert into holidays(hdate)
values(to_date('2009-04-04','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-04-05','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-04-06','yyyy-mm-dd'));-- 劳动节
insert into holidays(hdate)
values(to_date('2009-05-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-05-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-05-03','yyyy-mm-dd'));-- 端午节
insert into holidays(hdate)
values(to_date('2009-05-28','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-05-29','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-05-30','yyyy-mm-dd'));-- 国庆节、中秋节
insert into holidays(hdate)
values(to_date('2009-10-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-10-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-10-03','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-10-04','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-10-05','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-10-06','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-10-07','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2009-10-08','yyyy-mm-dd'));----------------------------------------------------------------
-- 2010年节假日
-- 元旦
insert into holidays(hdate)
values(to_date('2010-01-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-01-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-01-03','yyyy-mm-dd'));-- 春节
insert into holidays(hdate)
values(to_date('2010-02-13','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-02-14','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-02-15','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-02-16','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-02-17','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-02-18','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-02-19','yyyy-mm-dd'));-- 清明节
insert into holidays(hdate)
values(to_date('2010-04-03','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-04-04','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-04-05','yyyy-mm-dd'));-- 劳动节
insert into holidays(hdate)
values(to_date('2010-05-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-05-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-05-03','yyyy-mm-dd'));-- 端午节
insert into holidays(hdate)
values(to_date('2010-06-14','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-06-15','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-06-16','yyyy-mm-dd'));-- 中秋节
insert into holidays(hdate)
values(to_date('2010-09-22','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-09-23','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-09-24','yyyy-mm-dd'));-- 国庆节
insert into holidays(hdate)
values(to_date('2010-10-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-10-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-10-03','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-10-04','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-10-05','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-10-06','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2010-10-07','yyyy-mm-dd'));----------------------------------------------------------------
-- 2011年节假日-- 元旦
insert into holidays(hdate)
values(to_date('2011-01-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-01-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-01-03','yyyy-mm-dd'));-- 春节
insert into holidays(hdate)
values(to_date('2011-02-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-02-03','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-02-04','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-02-05','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-02-06','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-02-07','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-02-08','yyyy-mm-dd'));-- 清明节
insert into holidays(hdate)
values(to_date('2011-04-03','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-04-04','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-04-05','yyyy-mm-dd'));-- 劳动节
insert into holidays(hdate)
values(to_date('2011-04-30','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-05-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-05-02','yyyy-mm-dd'));-- 端午节
insert into holidays(hdate)
values(to_date('2011-06-04','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-06-05','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-06-06','yyyy-mm-dd'));-- 中秋节
insert into holidays(hdate)
values(to_date('2011-09-10','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-09-11','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-09-12','yyyy-mm-dd'));-- 国庆节
insert into holidays(hdate)
values(to_date('2011-10-01','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-10-02','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-10-03','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-10-04','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-10-05','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-10-06','yyyy-mm-dd'));
insert into holidays(hdate)
values(to_date('2011-10-07','yyyy-mm-dd'));
create or replace function wk_hours_between(
i_startTime varchar2, -- 起始时间:( 格式:'YYYY-MM-DD HH24:MI:SS' )
i_endTime varchar2 -- 结束时间:( 格式:'YYYY-MM-DD HH24:MI:SS' )
)
return number
is
v_real_startTime date;
v_real_endTime date;
v_hours number(18,0);
v_number number(18,4);
begin
v_real_startTime := to_date(i_startTime,'YYYY-MM-DD HH24:MI:SS');
v_real_endTime := to_date(i_endTime,'YYYY-MM-DD HH24:MI:SS'); if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间,将起始时间与结束时间替换
then
select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime from dual;
end if; if v_real_startTime > v_real_endTime -- 如果起始时间大于结束时间,将起始时间与结束时间替换
then
select v_real_startTime, v_real_endTime into v_real_endTime, v_real_startTime from dual;
end if; if v_real_startTime<trunc(v_real_startTime,'dd')+8/24 -- 如果小于当天8点,将其置为当天8天(因为你是8点上班)
then
v_real_startTime:=trunc(v_real_startTime,'dd')+8/24;
-- 如果大于当天12点,且小于当天14点,将其置为当天14点(因为你下午是14点上班)
elsif v_real_startTime>trunc(v_real_startTime,'dd')+12/24 and v_real_startTime<trunc(v_real_startTime,'dd')+14/24
then
v_real_startTime:=trunc(v_real_startTime,'dd')+14/24;
-- 如果大于当天18点,将其置为第二天8天
elsif v_real_startTime>trunc(v_real_startTime,'dd')+18/24
then
v_real_startTime:=trunc(v_real_startTime+1,'dd')+8/24;
end if; -- 如果小于当天8点,将其置为昨天18点
if v_real_endTime<trunc(v_real_endTime,'dd')+8/24
then
v_real_endTime:=trunc(v_real_endTime-1,'dd')+18/24;
-- 如果大于当天12点,且小于当天14点,将其置为当天12点(因为你上午是12点下班)
elsif v_real_endTime>trunc(v_real_endTime,'dd')+12/24 and v_real_endTime<trunc(v_real_endTime,'dd')+14/24
then
v_real_endTime:=trunc(v_real_endTime,'dd')+12/24;
elsif v_real_endTime>trunc(v_real_endTime,'dd')+18/24 -- 如果大于当天18点,将其置为当天18天(因为你是18点下班)
then
v_real_endTime:=trunc(v_real_endTime,'dd')+18/24;
end if; with a as( select v_real_startTime+(level-1)/24 as cdate,
trunc(v_real_startTime+(level-1)/24,'hh') as tr_cdate
from dual
connect by level <= (v_real_endTime-v_real_startTime)*24+2 ),
b as( select cdate, tr_cdate cdate2 from a where trunc(a.cdate) not in (select hdate from holidays) and to_char(a.cdate,'D') not in ('1','7') ), -- 排除周六、日 和 节假日
c as( select (case when to_char(t1.cdate,'hh24') in ('12','18') then trunc(t1.cdate,'hh') else t1.cdate end) as cdate1,
(case when to_char(t2.cdate,'hh24') in ('14') then trunc(t2.cdate,'hh') else t2.cdate end) as cdate2
from b t1
left join b t2 on trunc(t1.cdate,'hh24')=trunc(t2.cdate+1/24,'hh24')
order by (case when to_char(t1.cdate,'hh') in ('08','12','14','18') then trunc(t1.cdate,'hh') else t1.cdate end) ),
d as ( select (case when c.cdate1>v_real_endTime then v_real_endTime
else c.cdate1 end) as cdate1,
c.cdate2
from c
where to_char(c.cdate1,'hh24') in ('09','10','11','12','15','16','17','18') )
select sum(d.cdate1-nvl(d.cdate2,d.cdate1))*24 into v_number from d; return v_number;
end;
/-- *(03) 测试:
set serveroutput on;
select wk_hours_between('2011-03-21 08:00:00','2011-03-21 12:00:00') from dual;
create table 工作时间 (开始日期 date, 结束日期 date, 小时 number(2));
insert into 工作时间 values (to_date('2000-01-01','yyyy-MM-dd'), to_date('2010-12-31','yyyy-MM-dd'), 8);
insert into 工作时间 values (to_date('2011-01-01','yyyy-MM-dd'), to_date('2020-12-31','yyyy-MM-dd'), 7);create table 法定假日(日期 date);
insert into 法定假日 values ...;with 剩余日子 as (
select to_date('2011-03-21', 'yyyy-mm-dd') + rownum as 日期
from dual
connect by to_date('2011-03-21', 'yyyy-mm-dd') + rownum < to_date('2012-01-01', 'yyyy-mm-dd')
)
select
sum(工作时间.小时) as "还需要工作小时",
count(剩余日子.日期) as "还需要工作天数"
from 剩余日子
inner join 工作时间 on 剩余日子.日期 between 工作时间.开始日期 and 工作时间.结束日期
left join 法定假日 on 剩余日子.日期 = 法定假日.日期
where 法定假日.日期 is null and to_char(剩余日子.日期, 'D') between '2' and '6'
;
还需要工作小时 还需要工作天数
1358 194