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.
Best Answer
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.
Performing the query in the data the result is the following.