Web lists-archives.com

Re: Narrow A First Set Of Records




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