Sterling Lutes Sterling Lutes - 6 months ago 18
MySQL Question

Subquery Returns Multiple Records

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:
Table Records

Example of Output:
Output Example

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:

SELECT
@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


Sorry I do not have the SQL vocabulary to google this myself, I keep ending up on forums where they tell the op to limit the results of the subquery to 1, I need all of their breaks to return.

[Edit0: Just realized my second image has a misleading column. The last column should be length]

[Edit1: Sorry for the broken up link I just created my account and cannot post a link. Here is an sql fiddle example http://sqlfiddle.com/#!9/bc49e9/1/0 ]

SQLFiddle

Answer

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.

The 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.

The 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

Comments