Narrow A First Set Of Records

[Don Wieland]

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
~
d...@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 


Intermediate Developer Support Plan - 10 hours for $960 (2 FREE HOURS - $240 
savings off regular billable rate)
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 


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 

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

Narrow A First Set Of Records

[Don Wieland]

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

Re: Narrow A First Set Of Records

[Roy Lyseng]

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