Hey gang,

I need the ability to produce this end result:

condition a) All clients who have had at least 2 appointments in the last 24 months
condition b) but have NO appointments in the last 6 months

I am able to get the clients with >= 2 appointments with this query

SELECT
c.client_id,
concat(c.first_name, ' ', c.last_name) AS client_name,
c.`address`,
c.city,
c.state,
c.`zip`,
c.email ,
max(from_unixtime(a.time_start)) AS last_appt,
count(a.client_id) AS appt_count

FROM tl_clients c
LEFT JOIN tl_appt a ON c.client_id = a.client_id
GROUP BY a.client_id
HAVING max(a.time_start) >= UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 24 MONTH))
AND last_appt IS NOT NULL AND count(a.client_id) >= 2;

BUT, how do I add the other “condition b” to the query to get an accurate end result.

I’d appreciate any help.

Don Wieland
D W D a t a C o n c e p t s
~~~~~~~~~~~~~~~~~~~~~~~~~
donw@dwdataconcepts.com
http://www.dwdataconcepts.com
Direct Line - (949) 336-4828
SKYPE - skypename = dwdata

Integrated data solutions to fit your business needs.

Need assistance in dialing in your FileMaker solution? Check out our Developer Support Plans:

Basic Developer Support Plan - 3 hours @ $360:
http://www.dwdataconcepts.com/DevSup.php <http://www.dwdataconcepts.com/DevSup.php>

Intermediate Developer Support Plan - 10 hours for $960 (2 FREE HOURS - $240 savings off regular billable rate)
http://www.dwdataconcepts.com/IntDevSup.php <http://www.dwdataconcepts.com/IntDevSup.php>

Premium Developer Support Plan - 20 hours for $1800 ( 5 FREE HOURS - $600 savings off regular billable rate)
http://www.dwdataconcepts.com/PremDevSup.php <http://www.dwdataconcepts.com/PremDevSup.php>

In all of these plans, we create a support account and credit the account with the hours purchased. You can then dictate how and when the hours are used. They will not expire until they are used up and at that point you can opt to replenish the account, if you desire. When we work on your system or with you, we simply deduct the billable time from your account. At a regular interval or per your request, a summary of your account status will be email to you.

-------

Appointment 1.0v9 - Powerful Appointment Scheduling for FileMaker Pro 9 or higher
http://www.appointment10.com <http://www.appointment10.com/>

For a quick overview -
http://www.appointment10.com/Appt10_Promo/Overview.html