Calculating/Determine Hours on Nightshift with mul

Question

This are the logs of employee # 16

And here is my expected output

I've tried this query :

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------- |----------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 :(

Answer 1

Basically you have the following:

• a SQL statemeng giving start and end-times for any shift that might've been an nightshift,
• a calculation for the time difference.

Now, what is night-shift?

• all hours on start-day that occur after 10pm
• all hours on end-day that occur before 7am (guessing on my part)

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);

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