Hi Don,
On 17.12.15 16.14, Don Wieland wrote:
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) of the set of “condition a” rows, which of those 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
Can you try this query?
select * FROM (
select client_id, client_name, ...,
(select count(*) from tl_appt AS a
WHERE c.client_id = a.client_id AND
a.time_start >=
UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 24 MONTH)) AND
last_appt IS NOT NULL) AS twoyear,
(select count(*) from tl_appt AS a
WHERE c.client_id = a.client_id AND
a.time_start >=
UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 6 MONTH)) AND
last_appt IS NOT NULL) AS halfyear
from tl_clients AS c) AS dt
WHERE twoyear >= 2 AND halfyear == 0;
I have not run it through MySQL, so you may have to fix the syntax a bit...
Thanks,
Roy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
