I have been working with a query but need to add a few more
conditions. I was a to do a query that contains a few more selects but
want to know if there is a more efficient way to get results I need:
This query is to find NEW or FORMER CLIENT within a moving window
(Date Range) of time.
I am starting off with finding a group of:
paid (status = 3) appointments (appts)
within a DR
in a Location (r.location_id = 1)
by on or many users (apt_user_id)
---
SELECT c.client_id, c.first_name, c.last_name, apt.* FROM tl_appt apt
JOIN tl_rooms r on r.room_id = apt.room_id
JOIN tl_clients c on c.client_id = apt.client_id
JOIN tl_users u on u.user_id = apt.user_id
WHERE
apt.appt_id IS NOT NULL AND
apt.time_start between '1293858000' and '1325393999' AND
apt.appt_status_id = '3' and
r.location_id = '1' and
apt.appt_status_id IN (3) and
apt.user_id IN (506)
ORDER BY apt.user_id, apt.client_id, apt.time_start
---
From this above set of results, I want to find the earliest appt for
each client.
Then from that set of rows, I want to check to see if that client has
every had an appt earlier with ANY user, those appts can be earlier
than the start of the date range.
If they have a count of ZERO appts prior to the earliest appt within
the date range, the are a NEW CLIENT else a FORMER CLIENT.
Any assistance would be appreciated. Thanks!
Don Wieland
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
