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