Reej Reej - 8 months ago 71
MySQL Question

MYSQL Query to fetch the list of check-in and check out for each user

I have a table where the sensor will constantly insert data to the db with flag = 1 when a user is in the zone, and when he moves out of the zone, until it can read the card, it will send the data with flag = 0. how to fetch the pair of check-in and check-out for each user. The table structure is as belowenter image description here

Minor|Check-in|created
=====|========|=======
3002 |0 |2017-09-29 18:07:38
3002 |0 |2017-09-29 18:07:46
3002 |0 |2017-09-29 18:07:52
3002 |1 |2017-09-29 18:08:22
3002 |1 |2017-09-29 18:08:44
3002 |1 |2017-09-29 18:08:52
3002 |1 |2017-09-29 18:09:04
3002 |0 |2017-09-29 18:09:48
3002 |0 |2017-09-29 18:12:14
3002 |0 |2017-09-29 18:14:44
3002 |1 |2017-09-29 18:21:32
3002 |0 |2017-09-29 18:21:40
3002 |0 |2017-09-29 18:21:48
3002 |0 |2017-09-29 18:22:14


Expected output is:

Minor |status |time
3022 | 1 |2017-09-29 18:08:22
3022 | 0 |2017-09-29 18:09:48
3022 | 1 |2017-09-29 18:21:32
3022 | 0 |2017-09-29 18:21:40


Checkin checkout can be in 2 rows as above or in a single row as below,

Minor | check-in | Check-out
========|===================|===================
3022 |2017-09-29 18:08:22|2017-09-29 18:09:48
3022 |2017-09-29 18:21:32|2017-09-29 18:21:40


We need to fetch the check-in and check-out for each minor. Logic would be need to take one set of check-in and check-out for a minor, so the check-in will be the first created time which has check-in flag as 1, and check-out will be the created time for the minor whose check-in flag is 0.

Kindly advice

Thanks

Answer Source

E.g.:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(minor INT NOT NULL
,checkin TINYINT NOT NULL
,created  DATETIME NOT NULL
,PRIMARY KEY(minor,created)
);

INSERT INTO my_table VALUES
(3002,0,'2017-09-29 18:07:38'),
(3002,0,'2017-09-29 18:07:46'),
(3002,0,'2017-09-29 18:07:52'),
(3002,1,'2017-09-29 18:08:22'),
(3002,1,'2017-09-29 18:08:44'),
(3002,1,'2017-09-29 18:08:52'),
(3002,1,'2017-09-29 18:09:04'),
(3002,0,'2017-09-29 18:09:48'),
(3002,0,'2017-09-29 18:12:14'),
(3002,0,'2017-09-29 18:14:44'),
(3002,1,'2017-09-29 18:21:32'),
(3002,0,'2017-09-29 18:21:40'),
(3002,0,'2017-09-29 18:21:48'),
(3002,0,'2017-09-29 18:22:14');

SELECT checkin
     , created 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev <> checkin THEN @stamp:=created END stamp
            , @prev:=checkin prev 
         FROM my_table x
            , (SELECT @prev:=null,@created:=null) vars 
        ORDER 
           BY created
     ) a
 WHERE stamp IS NOT NULL
 ORDER 
    BY created;

+---------+---------------------+
| checkin | created             |
+---------+---------------------+
|       1 | 2017-09-29 18:08:22 |
|       0 | 2017-09-29 18:09:48 |
|       1 | 2017-09-29 18:21:32 |
|       0 | 2017-09-29 18:21:40 |
+---------+---------------------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download