How do I use MySQL Group By in this instance?

I have tables containing the following fields among others in a application for a transportation company...

jempe_users

+------------------------------+
| Field                        |
+------------------------------+
| user_id                      |
| user_user_group_id           |
| user_type                    | 
| user_real_name               |
| user_drv_code                |
+------------------------------+

jempe_user_types:

+---------------------+
| Field               |
+---------------------+
| user_type_id        |
| user_type_name      |
+---------------------+

employee_clock_events:

+-----------------------+
| Field                 |
+-----------------------+
| ece_id                |
| ece_employee_id       |
| ece_type              |
| ece_datetime          |
| ece_active            |
+-----------------------+

What I n开发者_JS百科eed to do is suggest three drivers that are currently working and not on break that can be assigned to pick up a patient.

The jempe_users tables contains employees as well as some clients of the transportation company. The user types table has a record where the user_type_name = driver and the user_type field in the jempe_users table references the jempe_user_types table.

The employee_clock_events table is used to clock employees in and out during their shift. The ece_type field is enum('shift_start', 'shift_brake', 'shift_resume', 'shift_stop'). The ece_datetime field stores a timesatmp of when the shift event took place.

So what I need to do is find driver user types in user_group 16, 17, or 18 that are currently working today and who's most recent shift ece_type is either 'shift_start' or 'shift_resume'.

I've tried dozens of variations of the following but can't seem to select employees WHO ARE CURRENTLY working. I seem to have no problems selected employees that have at least worked at some point today.

Tried variations of this...

SELECT user_id, driver_name, driver_code
FROM
(
    SELECT
        u.user_id AS user_id,
        u.user_real_name AS driver_name,
        u.user_drv_code AS driver_code,
        e.ece_type AS event_type,
        e.ece_datetime AS event_datetime
    FROM employee_clock_events AS e
    INNER JOIN jempe_users AS u 
        ON e.ece_employee_id = u.user_id
    LEFT JOIN jempe_user_types AS ut
        ON u.user_type = ut.user_type_id
    WHERE
        e.ece_shift_date = '2011-09-13'
        AND ut.user_type_name = 'driver'
        AND u.user_user_group_id IN (16, 17, 18)
        AND NOT ISNULL(u.user_drv_code)
        AND u.user_active = 1
) AS TEMPORARY
GROUP BY user_id
HAVING MAX(event_datetime)

And this...

SELECT u.user_id AS user_id, u.user_real_name AS driver_name, u.user_drv_code AS driver_code, MAX(e.ece_datetime) AS current_event_datetime
FROM employee_clock_events AS e
LEFT JOIN jempe_users AS u 
    ON e.ece_employee_id = u.user_id
LEFT JOIN jempe_user_types AS ut
    ON u.user_type = ut.user_type_id
WHERE
    (e.ece_type = 'shift_start' OR e.ece_type = 'shift_resume')
    AND e.ece_shift_date = '2011-09-13'
    AND ut.user_type_name = 'driver'
    AND u.user_user_group_id IN (16, 17, 18)
    AND NOT ISNULL(u.user_drv_code)
    AND u.user_active = 1
GROUP BY e.ece_employee_id

Where am I going wrong? Thanks for the help.

---

Your problem is determining what the most recent clock event is per employee. That part of the problem can be solved as follows:

 CREATE VIEW MRClockByEmployee (ece_employee_ID, ece_most_recent_datetime) AS
    AS SELECT ece_employee_ID, MAX(ece_datetime) 
    FROM employee_clock_events
    GROUP BY ece_employee_id;

 CREATE VIEW MRClockInfoByEmployee 
     (ece_id, ece_employee_ID, ece_type, ece_datetime, ece_active) AS
 SELECT CE.* FROM employee_clock_events CE JOIN MRClockByEmployee MR
    ON CE.ece_employee_ID = MR.ece_employee_id AND
       CE.ece_datetime = MR.ece_most_recent_datetime

 SELECT ece_employee_id FROM MRClockInfoByEmployee
    WHERE type IN ('shift_start', 'shift_resume')

You can then JOIN the contents of that second view back to your other tables to get the other information and take the TOP 3 records for your three possible drivers.

Note that you could do all that in a single, more complex, query but I think the concept of "employee current status" is important and reusable enough that it's worthwhile encapsulating the logic in a view.

The final solution (where you JOIN the tables together) will not use a GROUP BY. That is because GROUP BY must be used relatively early in the solution algorithm in order to create a list of employee ids and most recent datetime values; that list is then joined back to the employee table to get the a list of most recent records from that table.

---

I need to test this further but I ended up going with this...

SELECT u.user_id, u.user_real_name AS driver_name, u.user_drv_code AS driver_code
FROM
(
    SELECT ece_employee_id, MAX(ece_datetime) AS ece_datetime
    FROM employee_clock_events
    WHERE ece_active = 1 AND ece_shift_date = '2011-09-13'
    GROUP BY ece_employee_id
) AS mre
INNER JOIN employee_clock_events AS e
    ON e.ece_employee_id = mre.ece_employee_id AND e.ece_datetime = mre.ece_datetime
LEFT JOIN jempe_users AS u 
    ON e.ece_employee_id = u.user_id
LEFT JOIN jempe_user_types AS ut
    ON u.user_type = ut.user_type_id
WHERE
    (e.ece_type = 'shift_start' OR e.ece_type = 'shift_resume')
    AND e.ece_shift_date = '2011-09-13'
    AND ut.user_type_name = 'driver'
    AND u.user_user_group_id IN (16, 17, 18)
    AND NOT ISNULL(u.user_drv_code)
    AND u.user_active = 1

It seems to be working properly so far. I haven't been able to get Larry's view suggestion to work yet but I'll check it out more later. If it seems to work I'll mark his answer as correct. Does anyone see anything wrong with this?