Trying to find specific results in a database to have pairs of rows

user_11610503 0
Viewed 25

I have a timestamp column that has date and time, a column for the id of a employee, and a column representing whether or not the event is a 'clock in', or 'clock out' punch.

What I need to do is be able to find pairs of 'clock in' and 'clock out' rows. I need the 'clock in' row to be values at 7am, and clock out to be at 11.

I also need to be sure these rows have the same id, and the same date.

Ideally, a pair result would look something like this.

id timestamp event
1234 2021-04-03 07:01 clock in
1234 2021-04-03 11:34 clock out

How could I write this as a query so that I end up with pairs that look similar to the above? Otherwise, trying to find these specific results is like looking for a needle in a haystack.

mysql sql
1 Answers

You could use an aggregation approach:

SELECT id, timestamp, event
FROM yourTable
WHERE (HOUR(timestamp), event) IN ((7, 'clock in'), (11, 'clock out')) AND
      id IN (SELECT id
             FROM yourTable
             GROUP BY id
             HAVING SUM(HOUR(timestamp) = 7 AND event = 'clock in') > 0 AND
                    SUM(HOUR(timestamp) = 11 AND event = 'clock out') > 0);
user_18632290
Related