0

I have to implement an access control system for a company, that will be able to report the access of each employee in a specific area of the building in order to provide a report. One of the requirements is to provide a list of the employees with more than 3 hours overtime with in a given date time window.

The table schema of the table that holds the required information is the following. ACTION states if the employee entered or left a specific level of the building. The rest are the time, the employee and the level.

create table access_action
(
    ID          bigint auto_increment primary key,
    ACTION      int      null,
    TIMESTAMP   datetime null,
    level_id    bigint   null,
    employee_id bigint   null,
    constraint FK_access_action_employee_id
        foreign key (employee_id) references employee (ID),
    constraint FK_access_action_level_id
        foreign key (level_id) references level (ID)
);

a sample result set

ID  ACTION      TIMESTAMP       EMPLOYEE_ID
1   IN  2017-05-10 09:28:00 3   9
2   IN  2017-04-24 07:49:00 3   8
3   IN  2017-02-27 07:08:00 1   9
4   IN  2017-04-26 06:15:00 1   25
5   IN  2017-02-02 09:37:00 3   24
6   OUT 2017-05-29 08:03:00 3   7
7   IN  2017-04-25 07:07:00 1   1
8   IN  2017-01-09 08:54:00 3   8
9   IN  2017-05-12 07:57:00 2   1
10  OUT 2017-02-09 08:03:00 3   30
11  IN  2017-03-09 08:56:00 3   30
12  OUT 2017-02-03 11:26:00 3   5

I believe I know how I can estimate the overtime considering the sum up of the time each employee spent inside the premises of the building which will give the total hours. But I only can imagine how I could do it in the application layer.

Is there a way I can estimate the overtimes purely with SQL?

EDIT

According to the comments there must be a strict sequence among the enter - leave actions therefore a created the following data set.

10
  • Considering that empploee id 9 enters two times and never leaves , you should be more careful choosing your data,. More importantly which mysql version are you using? – nbk Apr 5 '20 at 19:01
  • @nbk You are right. version 5.7.27-0ubuntu0.18.04.1 – giannis christofakis Apr 5 '20 at 19:32
  • as you can see dbfiddle.uk/… there is no chance to even tell the time the were at the building at one level. Besides when is overtime after 8 hours with breaks. – nbk Apr 5 '20 at 19:51
  • @nbk as I wrote this is only a sample, I can provide the full data set but it would be massive and even then some inconsistent data might appear. Lets imagine there is a check mechanism that doesn't allow an employee to enter or leave the building without first checking his card. – giannis christofakis Apr 5 '20 at 20:02
  • 1
    or at least a hint on how to start? Use 2 table copies joined by employee - 1st for IN and 2nd for OUT. For each IN time its according OUT time is minimal time which is above. – Akina Apr 5 '20 at 22:10
0

Based on @Akina s comment, I created two sets one for the IN actions and another for the OUT, which I joined them and subtract the time spent inside the premises. Therefore I can calculate the total working hours for each employee in any particular day.

I came up with the following query.

SET @row_number = 0;
SET @row_number1 = 0;

select SEC_TO_TIME( SUM( TIME_TO_SEC(abb.overtimes) ) ) overtimes, employee_id
from
    (select TIMEDIFF(ab.working_time,'08:00:00') overtimes, ab.employee_id from
        (select SEC_TO_TIME( SUM(aaaa.timespent) ) working_time, accessDate, aaaa.employee_id
            from
            (select aaa.OUTtime as OUTtime, aaa.INtime, TIME_TO_SEC( TIMEDIFF(OUTtime, INtime) ) as timespent, DATE(OUTtime) as accessDate, aaa.level_id, aaa.employee_id
             from
                 (select aa1.TIMESTAMP as OUTtime, aa2.TIMESTAMP as INtime, aa1.level_id, aa1.employee_id
                  from (select
                            (@row_number:=@row_number + 1) row_num,
                            a1.TIMESTAMP, a1.employee_id, a1.level_id
                        from access_action a1
                        where a1.ACTION = 'OUT'
                        order by employee_id asc, TIMESTAMP desc) aa1
                           INNER JOIN
                       (select
                            (@row_number1:=@row_number1 + 1) row_num,
                            a2.TIMESTAMP, a2.employee_id, a2.level_id
                        from access_action a2
                        where a2.ACTION = 'IN'
                        order by employee_id asc, TIMESTAMP desc) aa2
                       ON aa1.row_num = aa2.row_num) aaa) aaaa
        group by accessDate, aaaa.employee_id
        having accessDate >= '2017-05-26' and accessDate <= '2017-05-30'
        AND working_time > '08:00:00') ab) abb
group by employee_id
having overtimes > '03:00:00';

Performing the query in the data the result is the following.

overtimes   employee_id
03:50:00    4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.