We have biometric data which contain three column Employee id, TimeIn, and autoincrement id. We have to find out the employee hours but the employee may be in and out two or three times in a day. How to find out the hours for his in and out entry. There is no column in which we say in and out. We also did max(TimeIn) and min(TimeIn) difference. But in our case, it is not possible because of an employee in and out at any time and many times in a day.
13 2017-10-31 23:15:11 1 255 1 0
13 2017-10-31 23:15:27 1 255 1 0
3 2017-11-01 05:54:06 1 255 1 0
2 2017-11-01 05:54:31 1 255 1 0
11 2017-11-01 05:55:58 1 255 1 0
1 2017-11-01 05:56:24 1 255 1 0
14 2017-11-01 06:02:23 1 255 1 0
8 2017-11-01 07:54:24 1 255 1 0
8 2017-11-01 07:54:27 1 255 1 0
7 2017-11-01 08:06:37 1 255 1 0
13 2017-11-01 08:29:52 1 255 1 0
10 2017-11-01 09:54:41 1 255 1 0
15 2017-11-01 10:44:17 1 255 1 0
4 2017-11-01 11:00:28 1 255 1 0
14 2017-11-01 11:00:59 1 255 1 0
5 2017-11-01 11:24:50 1 255 1 0
9 2017-11-01 12:46:32 1 255 1 0
6 2017-11-01 12:58:24 1 255 1 0
11 2017-11-01 13:00:18 1 255 1 0
1 2017-11-01 13:00:28 1 255 1 0
2 2017-11-01 16:06:14 1 255 1 0
2 2017-11-01 16:06:24 1 255 1 0
follow there is no in and out you can only assume first input as IN while next as Out.
you can inner join to next value to get time diff than filtering only odd value(even will be time out)
select a.row, a.empid, timestampdiff(second,a.timein, ifnull(b.timeout,date_add(date(a.timein) , interval 1 day))) timeinsec from
(SELECT @row_num := IF(@prev_value=o.empid,@row_num+1,1) AS Row,
empid ,timein,
@prev_value := o.empid
FROM Table1 o,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY o.empid,timein asc
) a
join
(SELECT @row_num := IF(@prev_value2=o.empid,@row_num+1,1) AS Row,
empid ,timein as timeout,
@prev_value2 := o.empid
FROM Table1 o,
(SELECT @row_num := 1) x,
(SELECT @prev_value2 := '') y
ORDER BY o.empid,timein asc) b
on a.empid=b.empid and a.row=b.row-1
where mod(a.row,2)=1