How to SELECT records into a single record to determine TIME difference

Question

I have a 3rd party clocking application which we use to track our staff's movements. It runs on a MariaDB, but it is pretty messy (imo). The frontend is windows based and all the processing happens there. Problem is I would like to extract some data for realtime reporting.

All clocking activities are saved to a single table. See sample below.

INDX    _DATE_      _TIME_      SURNAME WIEGANDID       ZKINOUT
51      2018/09/03  5:52:04     Thakadu 000000000000AE  0
198     2018/09/03  14:04:29    Thakadu 000000000000AE  1
309     2018/09/03  21:54:06    Mabeo   000000000000BA  0
370     2018/09/04  5:47:20     Thakadu 000000000000AE  0
401     2018/09/04  6:00:09     Mabeo   000000000000BA  1
557     2018/09/04  14:04:57    Thakadu 000000000000AE  1
691     2018/09/04  21:53:33    Mabeo   000000000000BA  0
748     2018/09/05  5:47:20     Thakadu 000000000000AE  0
780     2018/09/05  6:00:34     Mabeo   000000000000BA  1
946     2018/09/05  14:05:32    Thakadu 000000000000AE  1
1089    2018/09/05  21:49:48    Mabeo   000000000000BA  0
1144    2018/09/06  5:50:41     Thakadu 000000000000AE  0
1174    2018/09/06  6:00:16     Mabeo   000000000000BA  1
1328    2018/09/06  14:09:28    Thakadu 000000000000AE  1
1482    2018/09/06  21:50:32    Mabeo   000000000000BA  0
1568    2018/09/07  5:58:48     Thakadu 000000000000AE  0
1555    2018/09/07  6:01:01     Mabeo   000000000000BA  1
1812    2018/09/07  14:05:47    Thakadu 000000000000AE  1
1845    2018/09/07  21:51:31    Mabeo   000000000000BA  0

The challenge comes on where Mabeo works from 22:00:00 till 06:00:00 the next morning. Also, sometime staff don't clock out for some reason, the system then automatically logs them out, without logging a time.

The result I would like to see looks something like this.

DATE_IN     TIME_IN    DATE_OUT    TIME_OUT    SURNAME    WIEGANDID
2018/09/03  05:52:04   2018/09/03  14:04:29    Thakadu    000000000000AE    
2018/09/03  21:54:06   2018/09/04  06:00:09    Mabeo      000000000000BA
2018/09/04  05:47:20   2018/09/04  14:04:57    Thakadu    000000000000AE
2018/09/04  21:53:33   2018/09/05  06:00:16    Mabeo      000000000000BA

This way I can work out the actual time each emply was clocked in over a period of time.

I was able to get some success using GROUP BY and CASE, but the problem comes in on the employee working night shift.

Any help would be appreciated.

---------------------------------UPDATE-------------------------------

OK, so thank you very much to all who contributed. I've almost got the answer, except not yet 100% there. I've used the following code as suggested by @rf1234, thank you for the answer.

SELECT COALESCE (a.DATE_IN, b.DATE_IN)                  AS DATE_IN,
       SUBSTR(COALESCE (a.TIME_IN, b.TIME_IN), 1, 8)    AS TIME_IN,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN COALESCE (a.DATE_OUT, 
            b.DATE_OUT) ELSE '' END                     AS DATE_OUT,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN 
            SUBSTR(COALESCE (a.TIME_OUT, b.TIME_OUT), 1, 8) ELSE '' END
                                                        AS TIME_OUT,
       COALESCE (a.SURNAME, b.SURNAME)                  AS SURNAME,
       COALESCE (a.WIEGANDID, b.WIEGANDID)              AS WIEGANDID,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN
            TIMESTAMPDIFF(SECOND, a.DATE_TIME_IN, b.DATE_TIME_OUT) / 3600 ELSE '' END
                                                        AS HOURS_WORKED
FROM (
    SELECT  _DATE_  AS  DATE_IN,
            _TIME_  AS  TIME_IN,
            NULL    AS  DATE_OUT,
            NULL    AS  TIME_OUT,
            SURNAME,
            WIEGANDID,
            CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_IN
          FROM _2018_09
         WHERE ZKINOUT = 0 AND SURNAME = 'MABEO'
         GROUP BY WIEGANDID, _DATE_ )   AS   a,
    (

    SELECT  NULL    AS  DATE_IN,
        NULL    AS  TIME_IN,
        _DATE_  AS  DATE_OUT,
        _TIME_  AS  TIME_OUT,
        SURNAME,
        WIEGANDID,
        CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_OUT
      FROM _2018_09
     WHERE ZKINOUT = 1 AND SURNAME = 'MABEO'
     GROUP BY WIEGANDID, _DATE_)   AS   b
WHERE a.WIEGANDID = b.WIEGANDID
ORDER BY 1, 2, 3, 4

I've modified the code to only select a single employee, for now. The result I get is almost what I want, except it seems to join each record of table a to each record of table b? Below is image of a sample of the result.

Consider storing date and time as a single entity. It will massively simplify the problem. — Strawberry, Sep 26 '18 at 15:09
If you could convert the date and time to full timestamps, then you could select "CASE WHEN zkinout = 0 THEN -timestamp ELSE timestamp" and collect the sum of those values. That however won't account for a currently active shift. I'd personally be inclined to just load it up in a script and massage it there. — Jacob Ewing, Sep 26 '18 at 17:31

score 0 · Answer 1 · answered Sep 26 '18 at 17:32

I finished creating an app only last week to monitor my employees' punchIN and PunchOUT times to monitor their attendance, and hence their leaves taken , half days etc to further analyze the data.

Seeing your provided data, I would suggest what made my analytics app very simple.Use Timestamp datatypes for punchIN and PunchOUT, this step will not only merge your existing columns but will make your work obstacle free.

Using IN and OUT times, you can calculate the "working hours" (simple arithmetics !).You can add flags to see if a person has worked in night (using BETWEEN function), etc etc. Ready-to-use simple functions will lead the way!

Please let me know if you need ANY help...!!

rf1234 · Accepted Answer · 2018-10-04T12:44:21.643

As master ArSuKa is saying you could of course have this a lot easier by redesigning the database. But let's see whether we can get your result just with what you have. We also calculate the hours worked (float number) in case the shift has already ended by diffing the two timestamps in the temporary tables. Using temporary DATE_TIME columns resolves the issue with the employee working night shift. The calculation is right regardless of whether the shift ends on a different day.

SELECT COALESCE (a.DATE_IN, b.DATE_IN)                  AS DATE_IN,
       SUBSTR(COALESCE (a.TIME_IN, b.TIME_IN), 1, 8)    AS TIME_IN,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN COALESCE (a.DATE_OUT, 
            b.DATE_OUT) ELSE '' END                     AS DATE_OUT,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN 
            SUBSTR(COALESCE (a.TIME_OUT, b.TIME_OUT), 1, 8) ELSE '' END
                                                        AS TIME_OUT,
       COALESCE (a.SURNAME, b.SURNAME)                  AS SURNAME,
       COALESCE (a.WIEGANDID, b.WIEGANDID)              AS WIEGANDID,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN
            TIMESTAMPDIFF(SECOND, a.DATE_TIME_IN, b.DATE_TIME_OUT) / 3600 ELSE '' END
                                                        AS HOURS_WORKED
FROM (
    SELECT  _DATE_  AS  DATE_IN,
            _TIME_  AS  TIME_IN, 
            NULL    AS  DATE_OUT,
            NULL    AS  TIME_OUT,
            SURNAME,
            WIEGANDID,
            CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_IN
       FROM test 
      WHERE ZKINOUT = 0 )   AS   a,
    (
    SELECT  NULL    AS  DATE_IN,
            NULL    AS  TIME_IN, 
            _DATE_  AS  DATE_OUT,
            _TIME_  AS  TIME_OUT,
            SURNAME,
            WIEGANDID,
            CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_OUT
       FROM test
      WHERE ZKINOUT = 1 )   AS   b
WHERE a.WIEGANDID = b.WIEGANDID  
HAVING HOURS_WORKED < 15 AND HOURS_WORKED <> ''
ORDER BY 1, 2, 3, 4

This looks a bit complicated and it does the following. Two temporary tables a and b are created. These are the SELECT statements in (). One temporary table contains the login records and the other one the log out records. The respective "missing" fields are set to NULL.

Then these tables can be joined with each other using WIEGANDID. COALESCE makes sure the null values are filtered out and are replaced with the values from the other temporary table that contains the right value. The check for DATE_TIME_OUT to be greater than DATE_TIME_IN makes sure DATE_OUT and TIME_OUT stay empty if the respective worker hasn't logged out yet.

I added a new column to the result table: HOURS_WORKED. It contains the hours worked as a float number. Shifts that haven't ended yet are ignored in the results set (HOURS_WORKED <> ''). Too eliminate duplicates and avoid too much complexity we filter out resultset records whith HOURS_WORKED > 15 because that doesn't occur in reality and there is usually enough time between two shifts that we don't catch two short shifts. Not a general solution but one that works for this particular application.

You could use the above statement in a CREATE VIEW statement. Then you could work with the view instead of the original table which would help reduce complexity.

The picture below shows the result of the query above based on all of your sample records.

Thanks so much, I will give this a go this morning. The challenge with the DB design is that it's a proprietary system and I only have query rights on the DB. Changing DB I assume would crash the time management system. — George Gerber, Sep 27 '18 at 08:06
Just added the result picture and the use of SUBSTR for the time fields for better legibility of the time columns. — rf1234, Sep 27 '18 at 09:44
Thanks so much, I got the query to run and it seems like the information I require is there. The only issue is that it seems to be joining all the dates to each other, so I end up with 42356 records when the table contains 7730 records in total, so I would expect 3865 (in a perfect world). I think it's really close to the result I need, but just a small issue. It's almost like one needs to move to the next record once a record has been matched. — George Gerber, Oct 02 '18 at 12:14
I've updated my question above, with what I've managed to achieve to date. Thank you for your assistance, I've almost got the result I need. — George Gerber, Oct 02 '18 at 14:53
Thank you again for your assistance, it's been great. The reason for the GROUP BY is the raw data includes duplicates for the fingerprint reader for some reason, so the GROUP BY just eliminates those, and the NAME check is just for testing to reduce the number of records. I still have a problem, where with the new code added for some reason it doesn't recognise table a, error: 08:58:45 Kernel error: Error( 1146 ) 42S02: "Table 'aixaro.a' doesn't exist" Any thoughts? — George Gerber, Oct 04 '18 at 07:01
The example should work now. At least with all of the records you provided. I found an effective way to eliminate duplicates from the resultset without having to create views: Just check for HOURS_WORKED and eliminate records where the shift hasn't ended yet and the records that show too many hours worked due to duplication. — rf1234, Oct 04 '18 at 12:48
Thank you for your assistance, I've now got exactly the result I was looking for. Thanks again. — George Gerber, Oct 08 '18 at 07:23
Happy to help. Just one remark: In case this doesn't work due to frequent logins and logouts for breaks for example you won't be able to avoid having subselects (e.g. SELECT (MAX) date etc.) on the temporary tables. This will make the code look really messy und highly redundant unless you put these temporary tables into VIEWS. — rf1234, Oct 08 '18 at 07:51

How to SELECT records into a single record to determine TIME difference

2 Answers2