Query Help...

[dwdata]

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.

[adam_k]

If this was MS SQL I'd join 2 subqueries.
1st)

select user_id,client_id, min(time_start) as time_start
from tl_appt 
where time_start >= getdate()
group by client_id, user_id 

If you join all 3 fields returned your data will be limited only to the earliest appointment.

2a)

select client_id, count(*) from tl_appt 
group by client_id 

If count(*) = 1 then you've got a new client. I know it's not what you've asked for and could return wrong results if a new client booked more than 1 appts. So I've revised it to

2b)

select client_id, count(*) from tl_appt appt 
inner join (select user_id,client_id, min(time_start) as time_start
from tl_appt 
where time_start >= getdate()
group by client_id, user_id ) min_date
on appt.client_id = min_date.client_id 
and appt.time_start < min_date.time_start
group by client_id 

The same should work in MySQL (I've never worked with it), but somebody might offer a better way of doing it.