使用多个日志计算/确定夜班时间 [英] Calculating/Determine Hours on Nightshift with multiple logs

问题描述

这是员工#16 的日志

这是我的预期输出

我试过这个查询:

SELECT a.`id` AS employ_id, 
      ADDTIME(MIN(a.adjustedDateTime), '12:00:00') AS check_in_time,   
      ADDTIME(MAX(a.adjustedDateTime), '12:00:00') AS check_out_time,   
     TIMEDIFF(
        MAX(a.adjustedDateTime),
        MIN(a.adjustedDateTime)   ) AS working_time,   
      COUNT(0) AS report_times,   
   DATE_FORMAT(a.adjustedDateTime, '%Y-%m-%d') AS report_date 
 FROM   (SELECT 
    `EnNo` AS id,
    SUBTIME(
      CONCAT(DATE(DateTime), ' ', TIME(DateTime)), '12:00:00') AS adjustedDateTime   
      FROM
    bio) a  
   GROUP BY a.`id`,   
     DATE_FORMAT(a.adjustedDateTime, '%Y-%m-%d')  
        ORDER BY a.`id`, DATE('DateTime');

[这里是输出，不计算白班时间:

[And here is the output, not calculating the dayshift hours :

--- Empid - |----------TimeIn---------- |----------TimeOut------- |----------小时----- |

--- Empid - | ----------TimeIn---------- |----------TimeOut------- |----------Hours----- |

00000006   2017-05-15 18:14:13   2017-05-16 06:30:05   12:15:52.000000
00000006   2017-05-16 18:10:18   2017-05-17 05:30:50   11:20:32.000000
00000006   2017-05-18 08:30:05   2017-05-18 08:30:05   00:00:00.000000 
00000006   2017-05-18 15:30:05   2017-05-18 15:30:05   00:00:00.000000

谁能帮我解决这个问题?或者任何关于它的算法或任何东西的建议.谢谢.对不起，如果我不能发布图片，没有足够的声誉:(

Can anyone help me with this? or any suggestion with it's algorithm or anything. Thanks. Sorry if I can't post the images, not enough reputation :(

推荐答案

基本上你有以下几点:

• 给出可能是夜班的任何班次的开始和结束时间的 SQL 语句，
• 时差的计算.

现在，什么是夜班?

• 开始日晚上 10 点之后的所有时间
• 上午 7 点之前发生的所有结束时间(我的猜测)

因此，不是简单地使用工作开始"时间戳，而是使用此工作开始和晚上 10 点之间的最大值以及工作结束和早上 7 点之间的最小值:

So, instead of simply using "start of work" timestamp, you use the maximum between this start of work and 10pm and the minimum of end-of-work and 7am:

请注意，对于这个夜班计算，我必须添加一些条件来猜测哪个是可能的下班时间(中午之前)​​以及哪个是可能的开始工作时间(中午之后):

Note that I had to add some conditions to guess which is a probable end-of-work time (before noon) and which is a probable start-of-work-time (after noon) for this nightshift calculation:

create table worktime_stackoverflow (id int, DateTime datetime);
delete from worktime_stackoverflow where id > 0;
insert into worktime_stackoverflow values (1, '2017-05-15 18:14:13');
insert into worktime_stackoverflow values (2, '2017-05-16 06:30:05');
insert into worktime_stackoverflow values (3, '2017-05-17 22:30:45');
insert into worktime_stackoverflow values (4, '2017-05-18 09:30:05');
insert into worktime_stackoverflow values (5, '2017-05-20 10:45:15');
insert into worktime_stackoverflow values (6, '2017-05-20 19:31:25');
insert into worktime_stackoverflow values (5, '2017-05-21 16:45:15');
insert into worktime_stackoverflow values (6, '2017-05-22 03:17:25');


select DATE(w.DateTime) day_of_report
  # Start of nightshift
, CONCAT(DATE(MIN(w.DateTime)), ' ' , '22:00:00') start_nightshift,
  # actual start of work
       MIN(w.DateTime) start_of_work,
  # mathematical max() of the two
       GREATEST(
          CONCAT(DATE(MIN(w.DateTime)), ' ' , '22:00:00'),
          Min(w.DateTime)
        ) nightshift_work_start,
  # end of nightshift-hours
        CONCAT(ADDDATE(DATE(MIN(w.DateTime)), INTERVAL 1 DAY), ' ' , '07:00:00') end_nightshift
  # the following worklog entry (= end of work)
        ,(SELECT MIN(w2.DateTime) as following_time FROM worktime_stackoverflow w2 WHERE w2.DateTime > w.DateTime AND TIME(w2.DateTime) < '12:00:00'
                  AND DATEDIFF(w2.DateTime, w.DateTime) < 2) as end_of_work
  # mathematical minimum of these two
      ,LEAST(
           (SELECT MIN(w2.DateTime) as following_time FROM worktime_stackoverflow w2 WHERE w2.DateTime > w.DateTime AND TIME(w2.DateTime) < '12:00:00'
              AND DATEDIFF(w2.DateTime, w.DateTime) < 2),
          CONCAT(ADDDATE(DATE(MIN(w.DateTime)), INTERVAL 1 DAY), ' ' , '07:00:00')
          ) nightshift_work_end  
from worktime_stackoverflow w
   # make sure to not use end-of-nightshift loutout times
   where TIME(w.DateTime) > '12:00:00'
GROUP by DATE(w.DateTime);

对于最终解决方案，您必须添加员工 ID 等...

For final solution you'd have to add in employee id and so on...

这篇关于使用多个日志计算/确定夜班时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！