I have one table.a
with multiple columns, but only three are important for the case, lets name them time_stamp
, employee_number
and station_id
.
I got this query:
SELECT checktime AS time_stamp, userid AS employee_number, SN AS station_id
FROM checkinout;
to get this information:
+---------------------+-----------------+---------------+
| time_stamp | employee_number | station_id |
+---------------------+-----------------+---------------+
| 30/05/2015 8:01:07 | 5 | 6068144700170 |
| 31/05/2015 8:05:17 | 2 | 6068144700170 |
| 31/05/2015 17:03:47 | 2 | 6068144700170 |
| 31/05/2015 22:03:24 | 13 | 6068144700170 |
| 01/06/2015 02:30:34 | 13 | 6068144700170 |
| 01/06/2015 03:24:33 | 13 | 6068144700170 |
| 01/06/2015 07:14:24 | 13 | 6068144700170 |
| ... | ... | ... |
+---------------------+-----------------+---------------+
And what I need is to classify type
and status
of time_stamp
by sequence for each employee_number
on every 20 hours interval. Why not each day? Because some employees start labor at night and finish other day morning.
The classification should be made by time_stamp
count, like in case of 1 timestamp for employee.a then t=0/s=I, case of 2 timestamps for employee.a then t=0/s=I - t=0/s=O, case of 3 timestamps for employee.a then t=0/s=I - t=2/s=O - t=0/s=O, case of 4 timestamps for employee.a then t=0/s=I - t=2/s=O - t=2/s=I - t=0/s=O...
Example of needed output:
+---------------------+-----------------+---------------+---------+-----------+
| time_stamp | employee_number | station_id | type_id | status_id |
+---------------------+-----------------+---------------+---------+-----------+
| 30/05/2015 8:01:07 | 5 | 6068144700170 | 0 | I |
| 31/05/2015 8:05:17 | 2 | 6068144700170 | 0 | I |
| 31/05/2015 17:03:47 | 2 | 6068144700170 | 0 | O |
| 31/05/2015 22:03:24 | 13 | 6068144700170 | 0 | I |
| 01/06/2015 02:30:34 | 13 | 6068144700170 | 2 | O |
| 01/06/2015 03:24:33 | 13 | 6068144700170 | 2 | I |
| 01/06/2015 07:14:24 | 13 | 6068144700170 | 0 | O |
| ... | ... | ... | ... | ... |
+---------------------+-----------------+---------------+---------+-----------+
This questions help me, but did't solved entirely the issue:
MySQL: Get start & end timestamp for each day
Mysql Show records where timestamp interval is lower then 4 minutes
Solved! Based on this one: MySQL - Subtracting value from previous row, group by
CODE:
SELECT
t1.userid employee_number,
t1.checktime time_stamp,
@diference := IF( @lastUserid = t1.userid, TIMEDIFF(t1.checktime,@lastChecktime), 0 ) diference,
@lastUserid := t1.userid userid_test,
@lastChecktime := t1.checktime timestamp_test,
@status_id := CASE
WHEN @diference = '0' THEN 'I'
WHEN @diference BETWEEN '00:00:00' AND '12:00:00' THEN 'O'
WHEN @diference BETWEEN '12:00:01' AND '20:59:59' THEN 'I'
WHEN @diference > '26:00:01' THEN 'I'
WHEN @diference BETWEEN '21:00:00' AND '26:00:00' THEN 'O'
END as status_id,
@type_id := CASE
WHEN @diference = '0' THEN 0
WHEN @diference > '99:59:59' THEN 0
WHEN @diference BETWEEN '12:30:00' AND '99:59:59' THEN 0
WHEN @diference BETWEEN '06:30:00' AND '12:29:59' THEN 1
WHEN @diference BETWEEN '00:25:00' AND '06:29:59' THEN 2
WHEN @diference BETWEEN '00:00:15' AND '00:24:59' THEN 4
END as type_id,
t1.SN station_id
FROM
icdat.checkinout t1,
( SELECT @lastUserid := 0,
@lastChecktime := 0 ) SQLVars
ORDER BY t1.userid, t1.checktime;
It retrieves three additional columns (userid_test, timestamp_test and diference) but for me is fine, as I got type and status for each timestamp based on previous timestamp and time interval.