81,092
社区成员
发帖
与我相关
我的任务
分享
create table t_register(id varchar2(50), person_id varchar2(50), start_time date, end_time date);
create table t_holiday(holiday varchar2(8));
insert into t_register(id, person_id, start_time, end_time)
select 1, '1', to_date('201901010712', 'yyyymmddhh24mi'), to_date('201901012101', 'yyyymmddhh24mi') from dual union all
select 2, '1', to_date('201901021412', 'yyyymmddhh24mi'), to_date('201901032101', 'yyyymmddhh24mi') from dual union all
select 3, '1', to_date('201901040812', 'yyyymmddhh24mi'), to_date('201901062101', 'yyyymmddhh24mi') from dual union all
select 4, '2', to_date('201901010812', 'yyyymmddhh24mi'), to_date('201901012101', 'yyyymmddhh24mi') from dual union all
select 5, '2', to_date('201901020812', 'yyyymmddhh24mi'), to_date('201901021601', 'yyyymmddhh24mi') from dual
;
insert into t_holiday(holiday) values('20190104');
insert into t_holiday(holiday) values('20190105');
create or replace function f_test_0122(start_time in date, end_time date, st number, ed number) return number is
Result number;
begin
return
(
case
when to_char(start_time, 'sssss') <= st * 60 * 60 and to_char(end_time, 'sssss') >= st * 60 * 60
then least(to_char(end_time, 'sssss') - st * 60 * 60, (ed - st) * 60 * 60)
when to_char(start_time, 'sssss') >= st * 60 * 60 and to_char(start_time, 'sssss') <= ed * 60 * 60
then least(to_char(end_time, 'sssss'), ed * 60 * 60) - to_char(start_time, 'sssss')
else 0 end
)
;
end f_test_0122;
select*from t_register;
select*from t_holiday;
with tab1 as (
select t1.*,
to_char(trunc(t1.start_time) + level - 1, 'yyyymmdd') date_flag,
max(level) over(partition by t1.id) max_level,
level lv
from t_register t1
connect by level <= trunc(t1.end_time) - trunc(t1.start_time) + 1
and prior t1.rowid = t1.rowid
and prior t1.person_id = t1.person_id
and prior sys_guid() is not null
)
,tab2 as (
select t1.*,
decode(lv, 1, t1.start_time, trunc(t1.start_time)) + (lv - 1) true_start_time,
decode(lv, max_level, t1.end_time, trunc(t1.end_time) + 1 - 1 / 24 / 60 / 60) - (max_level - lv) true_end_time
from tab1 t1
)
, tab3 as (
select t1.*,
t2.*,
f_test_0122(t1.true_start_time, t1.true_end_time, 8, 12) +
f_test_0122(t1.true_start_time, t1.true_end_time, 13.5, 17.5) +
f_test_0122(t1.true_start_time, t1.true_end_time, 19, 21) time_work,
f_test_0122(t1.true_start_time, t1.true_end_time, 0, 7) +
f_test_0122(t1.true_start_time, t1.true_end_time, 21, 24) time_overtime
from tab2 t1, t_holiday t2
where 1 = 1
and t1.date_flag = t2.holiday(+)
order by t1.id, t1.lv
)
select t1.person_id,
round(sum(
case when t1.holiday is null and to_char(trunc(t1.true_start_time), 'd') not in ('1', '6')
then t1.time_work
else 0 end
) / ((4 + 4 + 2) * 60 * 60), 2) 工作日非加班工作时间,
round(sum(
case when t1.holiday is null and to_char(trunc(t1.true_start_time), 'd') not in ('1', '6')
then t1.time_work + t1.time_overtime
else 0 end
) / ((4 + 4 + 2) * 60 * 60), 2) 工作日总工作时间,
round(sum(
case when t1.holiday is not null
then t1.time_work + t1.time_overtime
else 0 end
) / ((4 + 4 + 2) * 60 * 60), 2) 节假日工作时间,
round(sum(
case when t1.holiday is null and to_char(trunc(t1.true_start_time), 'd') in ('1', '6')
then t1.time_work + t1.time_overtime
else 0 end
) / ((4 + 4 + 2) * 60 * 60), 2) 非节假日周末工作时间,
round(sum(
t1.time_work + t1.time_overtime
) / ((4 + 4 + 2) * 60 * 60), 2) 总工作时间
from tab3 t1
group by t1.person_id
;