List:General Discussion« Previous MessageNext Message »
From:Don Wieland Date:May 28 2012 3:03pm
Subject:Query help...
View as plain text  
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
Thread
Query help...Don Wieland28 May
  • Re: Query help...hsv28 May