I have a starting query that displays appointments and I am trying to expand to get different statistics:
SELECT apt.user_id, apt.appt_id, apt.client_id, apt.time_start AS stime, FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') AS ftime FROM tl_appt apt LEFT JOIN tl_rooms r on r.room_id = apt.room_id WHERE apt.appt_id IS NOT NULL AND apt.appt_status_id = '3' AND FROM_UNIXTIME(apt.time_start,'%Y-%m-%d') BETWEEN '2012-01-01' AND '2012-05-18' AND r.location_id = '2' AND apt.user_id IN (503) ORDER BY apt.client_id, stime;
Step 1) Based on the returned set of rows from the above query, I want to only show THE EARLIEST appt (based on apt.time_start AS stime) for EACH CLIENT FOR each USER (apt.user_id IN (503, 506, 700)).
Step 2) Then based on the returned set of rows from Step 1, I want to add a column called "client_type" that displays a value of "New" (if there is NO appointments prior to the date of that EARLIEST with ANY apt.user_id ELSE the value will be "Former".
Not sure if this can be handled via a JOIN in the original query or if need to do a SUB-SELECT.
Any assistance would be appreciated.