--可精确到每天的工作时间、全年法定休息日、节假日。结果返回小时数--前提条件：DIM_oa_TIME 包含每一天，并且is_work=1 工作日 =0 非工作日
--首先创建工作时间视图createorreplaceviewVM_WORKTIMEas
select1asid,'00:00:00'asbegintime,''||AM_WORK_BEGINTIME||':00'asendtime,0asworkhourfromWORKTIME_CURRENCY
whereorg_account_id='67086964711'andweek_day_name=1andyearin(selectmax(year)yearfromWORKTIME_CURRENCYwhereorg_account_id='67086964711')
unionall
select2asid,''||AM_WORK_BEGINTIME||':00'asbegintime,''||AM_WORK_ENDTIME||':00'asendtime,3asworkhourfromWORKTIME_CURRENCY
whereorg_account_id='67086964711'andweek_day_name=1andyearin(selectmax(year)yearfromWORKTIME_CURRENCYwhereorg_account_id='67086964711')
unionall
select3asid,''||AM_WORK_ENDTIME||':00'asbegintime,''||PM_WORK_BEGINTIME||':00'asendtime,0asworkhourfromWORKTIME_CURRENCY
whereorg_account_id='67086964711'andweek_day_name=1andyearin(selectmax(year)yearfromWORKTIME_CURRENCYwhereorg_account_id='67086964711')
unionall
select4asid,''||PM_WORK_BEGINTIME||':00'asbegintime,''||PM_WORK_ENDTIME||':00'asendtime,4asworkhourfromWORKTIME_CURRENCY
whereorg_account_id='67086964711'andweek_day_name=1andyearin(selectmax(year)yearfromWORKTIME_CURRENCYwhereorg_account_id='67086964711')
unionallselect5asid,''||PM_WORK_ENDTIME||':00'asbegintime,'23:59:59'asendtime,0asworkhourfromWORKTIME_CURRENCY
whereorg_account_id='67086964711'andweek_day_name=1andyearin(selectmax(year)yearfromWORKTIME_CURRENCYwhereorg_account_id='67086964711')
结果为：
-- 下面函数计算同一天内，两时间的工作时长
CREATEORREPLACEfunctiongetonedayworkhour(begintimeindate,endtimeindate)returnnumberas--一天内（工作日）从开始时间到结束时间的工作时长(返回值为多少天)v_begintime date;
v_endtime date;
v_nextdate date;--下一天0时v_date date;--当天0时beforeworkdaynumnumber(10,5);--开始时间与本区间（开始时间所在）结束时间相差时长endworkdaynumnumber(10,5);--结束时间所在区间的开始时间到结束时间相差时长workhournumnumber(10,5);--两时间相差几个时长workdaynumnumber(10,5);--两时间相差时长(天)id_order_befint;--记录开始时间VM_WORKTIME 区间号id_order_endint;--记录结束时间VM_WORKTIME 区间号beginv_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;ifv_begintimeisnullorv_endtimeisnullorv_begintime>v_endtime--开始时间大于结束时间thenworkhournum :=0;else--计算开始时间到本区间结束时间为多少时间（按天计算）selectidintoid_order_beffromVM_WORKTIMEwhereTo_date(To_char(v_begintime,'yyyy/mm/dd')||begintime,'yyyy/mm/dd hh24:mi:ss')<=v_begintimeandTo_date(To_char(v_begintime,'yyyy/mm/dd')||endtime,'yyyy/mm/dd hh24:mi:ss')>v_begintime;--开始日期在哪个时间区间ifid_order_bef<>2andid_order_bef<>4thenbeforeworkdaynum :=0;--select endtime into v_begintime from VM_WORKTIME where id=id_order_bef;--把本id_order_bef的endtime 作为v_begintime临时开始时间elseselectto_number(To_date(To_char(v_begintime,'yyyy/mm/dd')||endtime,'yyyy/mm/dd hh24:mi:ss')-v_begintime)--- v_begintime参数intobeforeworkdaynumfromVM_WORKTIMEwhereid=id_order_bef;--用本id_order_bef的endtime减v_begintime 计算本区间时间endif;--计算本区间开始时间到结束时间为多少时间（按天计算）selectidintoid_order_endfromVM_WORKTIMEwhereTo_date(To_char(v_endtime,'yyyy/mm/dd')||begintime,'yyyy/mm/dd hh24:mi:ss')=v_endtime;--结束日期在哪个时间区间ifid_order_end<>2andid_order_end<>4thenendworkdaynum :=0;--select endtime into v_endtime from VM_WORKTIME where id=id_order_end;--把本id_order_end的endtime 作为v_endtime临时开始时间elseselectto_number( v_endtime-To_date(To_char(v_endtime,'yyyy/mm/dd')||begintime,'yyyy/mm/dd hh24:mi:ss'))--- v_endtime参数intoendworkdaynumfromVM_WORKTIMEwhereid=id_order_end;--;--用本id_order_end的endtime减v_begintime 计算本区间时间endif;--计算开始时间到结束时间 中间有多少时间（按天计算）ifid_order_bef=id_order_endthenselectround(to_number( v_endtime-v_begintime),5)--- 在同一个区间 结束时间减开始时间intoworkdaynumfromdual;elseselectsum(nvl(workhour,0))/24intoworkdaynumfromVM_WORKTIMEwhereid>id_order_befandid
--下面函数计算，两时间的工作时长CREATEORREPLACEfunctiongetworkhour(begindateindate,enddateindate)returnnumberas--两时间间隔多少小时（返回结果为小时）v_begindate date;
v_enddate date;
v_nextdate date;--下一天0时v_date date;--当天0时beforeworkhournumnumber(10,5);--开始时间育当天24点两时间相差几个小时endworkhournumnumber(10,5);--结束时间与当天0点两时间相差几个小时workhournumnumber(10,5);--两时间相差几个小时workdaynumnumber(10,5);--两时间相差几个整天工作日daynumnumber(10,5);--两时间相差几个整天is_workint;--是否为非工作日beginv_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;ifv_begindateisnullorv_enddateisnullorv_begindate>v_enddate--开始日期大于结束日期时thenworkhournum :=0;elseifto_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--开始日期和结束日期时为同一天thenselectcount(*)intois_workfromDIM_oa_TIMEwherefdate=To_date(To_char(v_begindate,'yyyy/mm/dd'),'yyyy/mm/dd hh24:mi:ss')andis_work=1;--这天是否节假日ifis_work=0--or v_begindate is null or v_enddate is nullthenworkhournum :=0;elseworkhournum :=round(getonedayworkhour(v_begindate,v_enddate)*24,5) ;endif;elseselectcount(*)intois_workfromDIM_oa_TIMEwherefdate=To_date(To_char(v_begindate,'yyyy/mm/dd'),'yyyy/mm/dd hh24:mi:ss')andis_work=1;--开始日期是否节假日ifis_work=0--or v_begindate is nullthenbeforeworkhournum :=0;elsebeforeworkhournum :=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;endif;selectcount(*)intoworkdaynumfromDIM_oa_TIMEwherefdate>=v_begindateandfdate<=v_enddateandis_work=1;--工作日相差天数selectcount(*)intois_workfromDIM_oa_TIMEwherefdate=To_date(To_char(v_enddate,'yyyy/mm/dd'),'yyyy/mm/dd hh24:mi:ss')andis_work=1;--结束日期是否节假日ifis_work=0--or v_begindate is nullthen--如果v_enddate为节假日workdaynum不减1endworkhournum :=0;elseendworkhournum :=round(getonedayworkhour(v_date,v_enddate)*24,5)+endworkhournum;--下一天0时到结束时间的小时数workdaynum :=workdaynum-1;--如果v_enddate不为节假日workdaynum减1endif;--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'); --工作日相差天数endif;endif; workhournum :=nvl(workdaynum,0)*24+nvl(endworkhournum,0)+nvl(beforeworkhournum,0)+nvl(workhournum,0);returnworkhournum;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;
