I have a table named clock it tracks employees hours throughout the day. The records have a type of 0, 1, 2, or 3 being Start Shift, Start Break, End Break, End shift--In that Order. An employee can take multiple breaks per day. How do I return all of their breaks with the columns start_time, end_time, and length.
Example of Table Records:
Example of Output:
Here is the SQL Query im using currently and I had to delete the second break time (Record ID 44 and 45) to run this:
@start := (SELECT time as start FROM clock WHERE employeeID = 1 AND time BETWEEN
(SELECT time FROM clock WHERE employeeID = 1 AND type=0 ORDER BY UNIX_TIMESTAMP(time) DESC LIMIT 1) AND CURRENT_TIMESTAMP AND type = 1) as start_time,
@end := (SELECT time as end FROM clock WHERE employeeID = 1 AND time BETWEEN
(SELECT time FROM clock WHERE employeeID = 1 AND type=0 ORDER BY UNIX_TIMESTAMP(time) DESC LIMIT 1) AND CURRENT_TIMESTAMP AND type = 2) as end_time,
TIMEDIFF(@end, @start) as length
In order to do what you need with consecutive data you need to use a
LAG analytical function. The thing is Mysql doesn't support it yet. So you have to mimic its behavior.
LAG function basically allows you to use previous rows values (based in an order and grouping).
To do so in MySql you have to use some variables and subqueries. Here is the query you need:
SELECT t.employeeID, t.starttime, t.endtime, date_format(TIMEDIFF(t.endtime, t.starttime), '%H:%i:%s') as length FROM (SELECT clk.employeeID, clk.`type`, @lag AS starttime, (@lag := clk.`time`) AS endtime FROM (SELECT c.* FROM (SELECT @_type = NULL, @lag := NULL) vars, clock c ORDER BY c.employeeID, c.`time` ) clk WHERE (CASE WHEN @_type IS NULL OR @_type <> type THEN @lag := NULL ELSE NULL END) IS NULL AND (@_type := type) IS NOT NULL ) AS t WHERE t.`type` = 2 AND t.starttime IS NOT NULL;
See it working here: http://sqlfiddle.com/#!9/239b23/18
Explaining the filter I made on the outer query:
WHERE t.`type` = 2 AND t.starttime IS NOT NULL;
The behavior of this query without the filters is to get the a value in this case
endtime alias and its previous value. If you don't put this filter, the first row would came up with
null, 2016-06-01 16:04:07 for start and end (if you filter only by type 1,2), therefore we take out columns where start is null.
t.type = 2 filter will take out the unneeded values because it is "grouped" by employee and type. See how it would work without those filters here: http://sqlfiddle.com/#!9/239b23/21
Note that on that second link I added the filter
AND type in (1,2) that was not needed on the first one because of the outer filter
t.type = 2