SQL Query to Retrieve Latest Check-in an Check-out Times for Attendees

This query pulls each attendee’s latest check-in and check-out times as separate columns in a single row. This is helpful for tracking attendance during events.Here is an example output: 

SQL Query

SELECT 
    attendee.ATT_fname AS FirstName,
    attendee.ATT_lname AS LastName,
    registration.REG_code AS RegistrationCode,
    MAX(CASE WHEN checkin.CHK_in = 1 THEN checkin.CHK_timestamp END) AS CheckInTime,
    MAX(CASE WHEN checkin.CHK_in = 0 THEN checkin.CHK_timestamp END) AS CheckOutTime
FROM 
    wp_esp_checkin AS checkin
JOIN 
    wp_esp_registration AS registration ON checkin.REG_ID = registration.REG_ID
JOIN 
    wp_esp_attendee_meta AS attendee ON registration.ATT_ID = attendee.ATT_ID
GROUP BY 
    attendee.ATT_ID, attendee.ATT_fname, attendee.ATT_lname, registration.REG_code
HAVING 
    CheckInTime IS NOT NULL 
    AND 
    CheckOutTime IS NOT NULL
ORDER BY 
    attendee.ATT_lname, attendee.ATT_fname;

Explanation

• CheckInTime: Retrieves the latest check-in timestamp for each attendee.
• CheckOutTime: Retrieves the latest check-out timestamp for each attendee.
• GROUP BY: Ensures each row represents a unique attendee with one check-in and one check-out timestamp.
• HAVING: Filters results to include only attendees who have both check-in and check-out data.
• ORDER BY: Sorts results by last name and first name for easy viewing.

Use Cases

• Tracking attendance during events to confirm attendees' check-in and check-out times.
• Generating time logs for event management purposes.

This query can be run directly on your Event Espresso database to monitor attendee attendance efficiently.