I have a project I am working on and I need to extract the most recent date from each each equipment number. I can't get MAX() to function with this. Please see the following code. The idea is that it goes through the table and pulls each row and then compares the event_date for the most recent date for each eq_num.
SELECT e.event_id,
l.location,
e.event_date,
c.name,
q.eq_type,
e.pickup_date,
e.eq_to_c AS eq_num,
e.eq_from_c,
CASE
WHEN l.location = '' OR l.location IS NULL AND e.eq_to_c != e.eq_from_c THEN 'Out'
WHEN e.eq_from_c = '' THEN l.location
WHEN e.eq_from_c IS NULL OR e.eq_from_c = '' THEN l.location
WHEN e.eq_to_c = e.eq_from_c THEN 'Returned'
WHEN e.eq_to_c != e.eq_from_c THEN l.location
WHEN l.location = '' OR l.location IS NULL AND e.eq_to_c = e.eq_from_c THEN 'Returned'
END AS eq_status,
CASE
WHEN CAST(e.event_date AS DATE) < CAST(e.pickup_date AS DATE) THEN e.pickup_date
WHEN CAST(e.pickup_date AS DATE) < CAST(e.event_date AS DATE) THEN e.event_date
WHEN CAST(e.event_date AS DATE) = CAST(e.pickup_date AS DATE) THEN e.event_date
END AS event_date
FROM events e
LEFT JOIN location l ON (e.location_id = l.id)
LEFT JOIN clients c ON (e.event_client_id = c.id)
LEFT JOIN equip_type q ON (e.req_eq_type_id = q.eq_type_id)
ORDER BY eq_num, event_date DESC