Oracle 计算工时除去节假日 (精确到每天工作时间)

--可精确到每天的工作时间、全年法定休息日、节假日。结果返回小时数
--前提条件：DIM_oa_TIME 包含每一天，并且is_work=1 工作日 =0 非工作日 
--详见：https://www.cnblogs.com/xiaobaidejiucuoben/p/14630923.html


-- 首先创建工作时间视图
create or replace view  VM_WORKTIME as

    select 1 as id,' 00:00:00'  as begintime,' '||AM_WORK_BEGINTIME||':00' as endtime,0 as workhour from WORKTIME_CURRENCY 
    where org_account_id='67086964711' and week_day_name=1 and year  in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711')
    union all
    select 2 as id,' '||AM_WORK_BEGINTIME||':00' as begintime,' '||AM_WORK_ENDTIME||':00' as endtime,3 as workhour from WORKTIME_CURRENCY 
    where org_account_id='67086964711' and week_day_name=1 and year  in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711')
    union all
    select 3 as id,' '||AM_WORK_ENDTIME||':00' as begintime,' '||PM_WORK_BEGINTIME||':00' as endtime,0 as workhour from WORKTIME_CURRENCY 
    where org_account_id='67086964711' and week_day_name=1 and year  in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711')
    union all
    select 4 as id,' '||PM_WORK_BEGINTIME||':00' as begintime,' '||PM_WORK_ENDTIME||':00' as endtime,4 as workhour from WORKTIME_CURRENCY 
    where org_account_id='67086964711' and week_day_name=1 and year  in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711')
    union all
    select 5 as id, ' '||PM_WORK_ENDTIME||':00' as begintime,' 23:59:59' as endtime,0 as workhour from WORKTIME_CURRENCY 
    where org_account_id='67086964711' and week_day_name=1 and year  in (select max(year) year from WORKTIME_CURRENCY where org_account_id='67086964711')

结果为：

-- 下面函数计算同一天内，两时间的工作时长
CREATE OR REPLACE function getonedayworkhour(begintime in date,endtime in date) return number as
  -- 一天内（工作日）从开始时间到结束时间的工作时长(返回值为多少天)
  v_begintime date;
  v_endtime date;
  v_nextdate date;--下一天0时
  v_date date;--当天0时
  beforeworkdaynum number(10,5);--开始时间与本区间（开始时间所在）结束时间相差时长
  endworkdaynum number(10,5);--结束时间所在区间的开始时间到结束时间相差时长
  workhournum number(10,5);--两时间相差几个时长
  workdaynum number(10,5);--两时间相差时长(天)
  id_order_bef int; --记录开始时间VM_WORKTIME 区间号
  id_order_end int; --记录结束时间VM_WORKTIME 区间号
begin

    v_begintime :=begintime;
    v_endtime :=endtime;
    v_nextdate :=To_date(To_char(v_begintime, 'yyyy/mm/dd')||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss');-- 开始日期的23:59:59时  --To_date(To_char(Trunc(v_begintime+1), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss') 
    v_date :=To_date(To_char(Trunc(v_endtime), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss');-- 结束日期的当天0时
    beforeworkdaynum :=0;
    endworkdaynum :=0;
    workhournum :=0;

    if v_begintime is null or v_endtime is null or v_begintime>v_endtime --开始时间大于结束时间
    then
        workhournum :=0;
    else
        --计算开始时间到本区间结束时间为多少时间（按天计算）
        select  id into id_order_bef 
        from VM_WORKTIME where To_date(To_char(v_begintime, 'yyyy/mm/dd')|| begintime, 'yyyy/mm/dd hh24:mi:ss')<=v_begintime 
        and  To_date(To_char(v_begintime, 'yyyy/mm/dd')|| endtime, 'yyyy/mm/dd hh24:mi:ss')>v_begintime; --开始日期在哪个时间区间

        if id_order_bef<>2 and id_order_bef<>4
        then
            beforeworkdaynum :=0;
            --select  endtime into v_begintime from VM_WORKTIME where id=id_order_bef;--把本id_order_bef的endtime 作为v_begintime临时开始时间
        else
            select to_number(To_date(To_char(v_begintime, 'yyyy/mm/dd')|| endtime, 'yyyy/mm/dd hh24:mi:ss')-
            v_begintime) ---  v_begintime参数
            into beforeworkdaynum from VM_WORKTIME where id=id_order_bef;--用本id_order_bef的endtime减v_begintime 计算本区间时间
        end if; 

        --计算本区间开始时间到结束时间为多少时间（按天计算）
        select  id into id_order_end 
        from VM_WORKTIME where To_date(To_char(v_endtime, 'yyyy/mm/dd')|| begintime, 'yyyy/mm/dd hh24:mi:ss')<v_endtime 
        and  To_date(To_char(v_endtime, 'yyyy/mm/dd')|| endtime, 'yyyy/mm/dd hh24:mi:ss')>=v_endtime; --结束日期在哪个时间区间

        if id_order_end<>2 and id_order_end<>4
        then
            endworkdaynum :=0;
            --select  endtime into v_endtime from VM_WORKTIME where id=id_order_end;--把本id_order_end的endtime 作为v_endtime临时开始时间
        else
            select to_number( v_endtime-To_date(To_char(v_endtime, 'yyyy/mm/dd')|| begintime, 'yyyy/mm/dd hh24:mi:ss')) ---  v_endtime参数
            into endworkdaynum 
            from VM_WORKTIME where id=id_order_end;  -- ;--用本id_order_end的endtime减v_begintime 计算本区间时间
        end if; 

        -- 计算开始时间到结束时间 中间有多少时间（按天计算）
        if id_order_bef=id_order_end 
        then
            select round(to_number( v_endtime-v_begintime),5) --- 在同一个区间 结束时间减开始时间
            into workdaynum  from dual;     
        else
            select sum(nvl(workhour,0))/24 into workdaynum from VM_WORKTIME where id >id_order_bef and id<id_order_end;
            --总共时间
            workdaynum :=nvl(workdaynum,0)+nvl(beforeworkdaynum,0)+nvl(endworkdaynum,0);
        end if;
    end if;      

    return workdaynum;
end;

---select getonedayworkhour(to_date('2022-05-27 09:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2022-05-27 23:30:00','yyyy-mm-dd hh24:mi:ss')) from  dual;

--select To_date(To_char('2022-05-27 23:30:00', 'yyyy/mm/dd')||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss') from dual;

--select getonedayworkhour(to_date('2022-06-02 16:34:21','yyyy-mm-dd hh24:mi:ss'),to_date(' 2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss')) from dual;

-- 下面函数计算，两时间的工作时长

CREATE OR REPLACE function getworkhour(begindate in date,enddate in date) return number as
-- 两时间间隔多少小时（返回结果为小时）
  v_begindate date;
  v_enddate date;
  v_nextdate date;--下一天0时
  v_date date;--当天0时
  beforeworkhournum number(10,5);--开始时间育当天24点两时间相差几个小时
  endworkhournum number(10,5);--结束时间与当天0点两时间相差几个小时
  workhournum number(10,5);--两时间相差几个小时
  workdaynum number(10,5);--两时间相差几个整天工作日
  daynum number(10,5);--两时间相差几个整天
  is_work int;--是否为非工作日
begin

    v_begindate :=begindate;
    v_enddate :=enddate;
    v_nextdate :=To_date(To_char(v_begindate, 'yyyy/mm/dd')||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss');-- 开始日期的下一天0时
    v_date :=To_date(To_char(Trunc(v_enddate), 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss');-- 结束日期的当天0时
    beforeworkhournum :=0;
    endworkhournum :=0;
    workhournum :=0;
    workdaynum :=0;
    daynum :=0;

    if v_begindate is null or v_enddate is null or v_begindate>v_enddate --开始日期大于结束日期时
    then
        workhournum :=0;
    else
        if to_number(To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss')-To_date(To_char(v_enddate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss'))=0 --开始日期和结束日期时为同一天
        then
            select  count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --这天是否节假日
            if is_work=0 --or v_begindate is null or v_enddate is null 
            then
                workhournum :=0;
            else
                workhournum := round(getonedayworkhour(v_begindate,v_enddate)*24,5) ;
            end if;  
        else 

            select  count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_begindate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --开始日期是否节假日
            if is_work=0 --or v_begindate is null
            then
                beforeworkhournum :=0;
            else
                beforeworkhournum :=round(getonedayworkhour(v_begindate,v_nextdate)*24,5)+beforeworkhournum;-- 开始时间到今天天最后一秒的小时数

                -- select round(getonedayworkhour(to_date('2022-05-26 08:30:00','yyyy-mm-dd hh24:mi:ss'),To_date(To_char(to_date('2022-05-26 08:30:00','yyyy-mm-dd hh24:mi:ss'), 'yyyy/mm/dd')||' 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))*24,5) from dual;

            end if; 

            select count(*) into workdaynum from DIM_oa_TIME where fdate>=v_begindate and fdate<=v_enddate and is_work=1; --工作日相差天数
            select  count(*) into is_work from DIM_oa_TIME where fdate=To_date(To_char(v_enddate, 'yyyy/mm/dd'), 'yyyy/mm/dd hh24:mi:ss') and is_work =1; --结束日期是否节假日 
            if is_work=0 --or v_begindate is null
            then
                --如果v_enddate为节假日workdaynum不减1
                endworkhournum :=0;
            else
                endworkhournum :=round(getonedayworkhour(v_date,v_enddate)*24,5)+endworkhournum;-- 下一天0时到结束时间的小时数
                workdaynum :=workdaynum-1;--如果v_enddate不为节假日workdaynum减1
            end if;  
            -- daynum :=to_date(To_char(v_enddate, 'yyyy/mm/dd'),'yyyy/mm/dd') - to_date(To_char(v_begindate, 'yyyy/mm/dd'),'yyyy/mm/dd'); --工作日相差天数            
        end if;    
    end if;
    workhournum :=nvl(workdaynum,0)*24+nvl(endworkhournum,0)+nvl(beforeworkhournum,0)+nvl(workhournum,0);
    return workhournum;
end;

---select getworkhour(to_date('2022-06-02 16:34:21','yyyy-mm-dd hh24:mi:ss'),to_date(' 2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss')) from  dual;

--workhournum := round(getonedayworkhour(v_begindate,v_enddate)*24,5) ; 

--select (to_date(' 2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss')-to_date('2022-06-02 16:34:21','yyyy-mm-dd hh24:mi:ss')) from dual;

--select round(getonedayworkhour(to_date('2022-06-02 09:58:58','yyyy-mm-dd hh24:mi:ss'),to_date(' 2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss'))*24,5) from dual;

--select getonedayworkhour(to_date('2022-06-02 16:34:21','yyyy-mm-dd hh24:mi:ss'),to_date('2022-06-02 16:58:58','yyyy-mm-dd hh24:mi:ss'))*24 from dual;