I have a database that stores the employee's check-ins and check-outs.
Up until now, everything was going great and there was no problem. That's when the management decided to go 24x7.
Now, I have a problem. Why? Let's get to that.
Previously, since the company was working like any other company. So the attendance of employees was easy to process.
Suppose I have data of one employee like this
id |empid|person |authdatetime |authdate |authtime|status |
-----+-----+--------------+-------------------+----------+--------+----------+
36409|F157 |Mohamed Mufeed|2021-08-01 17:28:50|2021-08-01|17:28:50|Check-out |
36524|F157 |Mohamed Mufeed|2021-08-02 09:37:37|2021-08-02|09:37:37|Check-in |
36710|F157 |Mohamed Mufeed|2021-08-02 17:27:40|2021-08-02|17:27:40|Check-in |
36711|F157 |Mohamed Mufeed|2021-08-02 17:27:48|2021-08-02|17:27:48|Check-out |
36789|F157 |Mohamed Mufeed|2021-08-03 08:02:40|2021-08-03|08:02:40|Check-in |
36998|F157 |Mohamed Mufeed|2021-08-03 17:28:52|2021-08-03|17:28:52|Check-out |
37611|F157 |Mohamed Mufeed|2021-08-07 08:07:17|2021-08-07|08:07:17|Check-in |
39952|F157 |Mohamed Mufeed|2021-08-17 13:22:21|2021-08-17|13:22:21|Check-in |
40592|F157 |Mohamed Mufeed|2021-08-19 12:13:14|2021-08-19|12:13:14|Check-in |
40784|F157 |Mohamed Mufeed|2021-08-21 08:06:08|2021-08-21|08:06:08|Check-in |
40841|F157 |Mohamed Mufeed|2021-08-21 13:05:42|2021-08-21|13:05:42|Check-in |
40854|F157 |Mohamed Mufeed|2021-08-21 14:21:04|2021-08-21|14:21:04|Check-in |
40865|F157 |Mohamed Mufeed|2021-08-21 15:33:45|2021-08-21|15:33:45|Check-out |
I can get the employee's check-in and check-out by grouping the data per day taking the first and last checks. I didn't even have to look at the status field if its a check-in or check-out.
So once this data is processed I would have the employee's attendance for the day like this.
id |machine_id|date |status|punchin |punchout|duration|
-----+----------+----------+------+--------+--------+--------+
13579|F157 |2021-08-01|P |17:28:50|17:28:50|00:00:00|
13661|F157 |2021-08-02|P |09:37:37|17:27:40|07:50:03|
13744|F157 |2021-08-03|P |08:02:40|17:28:52|09:26:12|
13940|F157 |2021-08-07|P |08:07:17|08:07:17|00:00:00|
14569|F157 |2021-08-17|P |13:22:21|13:22:21|00:00:00|
14757|F157 |2021-08-19|P |12:13:14|12:13:14|00:00:00|
14848|F157 |2021-08-21|P |08:06:08|15:33:45|07:27:37|
So anything that is a duration of 00:00 is considered a missing punch.
Great.
Now you see the problem. The management decided to go 24X7 so I have to get the attendance right.
Since I don't have a definite boundary of one day(00:00:00 - 23:59:59) now, the data is continuous.
So How do I get the employee's attendance data? Some employees may come like (9:00 PM - 02:00 AM).
I have developed a simple shifts mechanism so they can assign shifts to employees. But they are not punctual - so I can't rely on them to make this work. However, something in my gut says it's the only way.
The shifts table looks like this.
employee_id|date |from |till |duration|
-----------+----------+--------+--------+--------+
F157 |2021-08-01|08:00:00|17:00:00|09:00:00|
F157 |2021-08-02|08:00:00|17:00:00|09:00:00|
F157 |2021-08-03|08:00:00|17:00:00|09:00:00|
F157 |2021-08-06|08:00:00|17:00:00|09:00:00|
Here are some of the challenges that I am facing.
- Employee may forget to check in or check out or both.
- Employees can do multiple check-in and check-out.
So how can I answer these questions through the query?
- How many employees are late today?
- Who are the employees who left early from work yesterday?
- Who has a missing punch?
- Who stayed overtime?
Now I don't have a definite boundary of one day - I just know that I now need to process the data one by one.
So how can I look ahead and behind and identify if this check-out belongs to this day or yesterday ? & if there is a check-in missing or if there are multiple check-ins and so on?
I was not able to find anything to refer to, So if any of you knows any resources that would help also.
Here are some of the details regarding the requirements.
In the ideal case the employee will have only one check-in and one check-out. This is 80% of the scenario and it would be as per the assigned shift without much (about 1/2 an hour tolerance) deviation.
In case the employee forgot to check out the system should show that the check-out is missing and the effective duration would not be calculated. The check-out and check-in are matched against the shift that is defined in the shifts table that I have provided sample data for.
In case the employee forgot to check in - this is also the same scenario as check-out.
In case the employee forgot to check in and check out, that employee would be considered absent for the day.
I hope this answers some of dina's questions.