3

I am developing an timekeeping system which has a day and a night shift schedule.

Here is my table [tbl_datetime_records]

+---------------+---------------+--------------------+
| biometrics_id | employee_name |   date_time_logs   |
+---------------+---------------+--------------------+
| 1221          | EMPLOYEE 1    | 2014-07-11 09:00   |
| 1221          | EMPLOYEE 1    | 2014-07-11 18:00   |
| 1221          | EMPLOYEE 1    | 2014-07-12 08:54   |
| 1221          | EMPLOYEE 1    | 2014-07-12 18:01   |
| 7445          | EMPLOYEE 2    | 2014-07-11 09:00   |
| 7445          | EMPLOYEE 2    | 2014-07-11 18:00   |
| 7445          | EMPLOYEE 2    | 2014-07-12 08:54   |
| 7445          | EMPLOYEE 2    | 2014-07-12 18:01   |
| 2332          | EMPLOYEE 3    | 2014-07-11 18:00   |
| 2332          | EMPLOYEE 3    | 2014-07-12 02:00   |
| 2332          | EMPLOYEE 3    | 2014-07-12 02:01   |
| 2332          | EMPLOYEE 3    | 2014-07-12 18:00   |
| 2332          | EMPLOYEE 3    | 2014-07-13 02:03   |
| 4114          | EMPLOYEE 4    | 2014-07-11 08:43   |
| 4114          | EMPLOYEE 4    | 2014-07-11 08:44   |
| 4114          | EMPLOYEE 4    | 2014-07-11 14:02   |
| 4114          | EMPLOYEE 4    | 2014-07-12 08:56   |
| 4114          | EMPLOYEE 4    | 2014-07-12 18:04   |
| 4114          | EMPLOYEE 4    | 2014-07-13 09:00   |
+---------------+---------------+--------------------+

I am filtering this as FIRST IN and LAST OUT

Conditions:

  • There will be multiple IN and OUT for same day for employees. So the rules would be first IN and Last Out.
  • there will be a night shift sched filtering.
  • Time in and Time out can be null for an employee.
  • Their unique identification would be the (biomterics_id) field.

The Output should be:

+---------------+---------------+------------------+------------------+
| biometrics_id | employee_name |  date_time_in    |  date_time_out   |
+---------------+---------------+------------------+------------------+
| 1221          | EMPLOYEE 1    | 2014-07-11 09:00 | 2014-07-11 18:00 |
| 1221          | EMPLOYEE 1    | 2014-07-12 08:54 | 2014-07-12 18:01 |
| 7445          | EMPLOYEE 2    | 2014-07-11 09:00 | 2014-07-11 18:00 |
| 7445          | EMPLOYEE 2    | 2014-07-12 08:54 | 2014-07-12 18:01 |
| 2332          | EMPLOYEE 3    | 2014-07-11 18:00 | 2014-07-12 02:01 | ---> NIGHT SHIFT
| 2332          | EMPLOYEE 3    | 2014-07-12 18:00 | 2014-07-13 02:03 | ---> NIGHT SHIFT
| 4114          | EMPLOYEE 4    | 2014-07-11 08:43 | 2014-07-11 14:02 | ---> HALF DAY
| 4114          | EMPLOYEE 4    | NULL             | 2014-07-12 18:04 | ---> NO TIME IN
| 4114          | EMPLOYEE 4    | 2014-07-1309:00  | NULL             | ---> NO TIME OUT
+---------------+---------------+------------------+------------------+

I don't still have any idea on this scenario. I am using MYSQL/PHP/LARAVEL

Rick James
  • 135,179
  • 13
  • 127
  • 222
Clintuy
  • 69
  • 8
  • What is the datatype of your `time_in` and `time_out` column ? It does not look like storing in proper MySQL datetime format – Madhur Bhaiya Sep 25 '19 at 10:25
  • the datatype of time_in and time_out is "DATETIME" – Clintuy Sep 25 '19 at 10:26
  • Offtopic: please fix your data example and expected results to match that datatype.. – Raymond Nijland Sep 25 '19 at 10:26
  • @Clintuy your date format seems a bit odd for `DATETIME`. – Script47 Sep 25 '19 at 10:27
  • 1
    Ontopic: this seams to me to be as simple as finding a min and max per `(biometrics_id | employee_name, DATE(date_time_logs))` group or i am missing something every obvious.. Also don't expect answers as your data example is not valid as you claimed to use `DATETIME` datatype this is not a valid format for that datatype.. – Raymond Nijland Sep 25 '19 at 10:33
  • Ok i missed something obvious `(biometrics_id | employee_name, DATE(date_time_logs))` group ofcource will not work for the night shifts.. Annyhow which MySQL version ? `SELECT VERSION();` as MySQL 8 would make this much more easy with `LAG()/ LEAD()` – Raymond Nijland Sep 25 '19 at 10:38
  • 10.1.38-MariaDB – Clintuy Sep 25 '19 at 10:39
  • The problem here is that your BIOMETRICS DATA doesnt have direction or biometrics number. It seems like this impossiblile to solve. Beacuse there is a possibility that a user is performing DOUBLE LOG-IN or CHEATING his/her way in. Over the years i've been working with biometrics data, for me those direction/biometrics terminal code data is extremely important. As much as i want to help you with this, I'll pass on this one. I can't help you. – dodzb Sep 25 '19 at 10:46
  • our BIOMETRICS DATA has AccessReader like if it is (In or Out) but the problem is the employees mistaken to switch AccessReader from in or out. example scenario: employee 1 would use time in AccessReader but instead of in reader he switched to out.... – Clintuy Sep 25 '19 at 10:52
  • 1
    software should not be smart enough to fix logistics problems or human errors as that is a losing battle not to mention hard to program right... Also what @dodzb said is also true employees also can mistaken by double log in or double log outs.. – Raymond Nijland Sep 25 '19 at 10:58
  • It is best to resolve this on the INPUT of these data. For me, I cannot trust the data you presented right now. Sometimes we developers get in trouble for representing wrong information from the unformated collected data. If you have way to find the IN/OUT data of these entries, probably you should do that as well. – dodzb Sep 25 '19 at 11:02
  • See the tag [unpivot] – Rick James Oct 07 '19 at 00:36

0 Answers0