如何从两个时间段里统计出员工平时工作时间，周末加班时间，法定假日加班时间？

[nimeide1234567890]

如何从两个时间段里统计出员工平时工作时间，周末加班时间，法定假日加班时间？

节假日我有建立一个表，哪天是周末，哪天是国家法定假日，

员工上班时间是8点到12点，1点半到5点半，晚上7点到9点，一周上班5天半，

然后员工从下午14点工作到了第二天上午10点10分下班，也就是员工打了2次卡，一次时间是下午14点，一次时间是第二天上午10点10分，
下班时间不算，
SQL SERVER 2000，
怎么计算出这个员工平时工作了多少小时？
周末工作了多少小时（员工也可能周末加班）？
国家法定假日工作了多少小时（员工也可能法定假日加班）？

[十八道胡同]

如果用脚本来写，用存储过程 是一个比较好的办法

[nayi_224]

别！如果这种sql都写不出来，还是别搞存储过程了，用js都比那强。如果你说的是函数，那倒是可以考虑一下。

[nayi_224]

。。。好像是我想简单了，竟然写了一小时才搞定。 加了点东西，0~7,21~24点也算作加班时间，并考虑到了工作时间超过24小时的情况。 没sqlserver，用oracle写的，但是没用什么复杂的语法。

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
;

[丿夜曲]

有打卡记录表就好算了，比如这个
【员工从下午14点工作到了第二天上午10点10分下班，也就是员工打了2次卡，一次时间是下午14点，一次时间是第二天上午10点10分】
14点打卡一次，迟到半小时，计数4小时，10点10分再打卡，迟到2小时10分，计数1小时50分钟，总共工作5小时50分钟

[nayi_224]

数据记录有问题。如果员工连续两天从14点工作到10点，可能会识别为员工从10点工作到14点。除非你是上班和下班分为两个字段存储。 建议你把表结构发出来，再加点测试数据吧，不然有歧义。

[maradona1984]

写sql做这事挺吃力的,还是用代码做吧
还有一些边界问题要解决,比如一个人加班到凌晨4点,刚好这一天是节假日,这个怎么算~
如果这些问题搞定,直接遍历上班日期即可,分开统计

写sql搞这个实在是太痛苦,如果实在不行,就写存储过程吧