Page 1 of 1

Cant figure out query

Posted: Mon Oct 01, 2012 3:02 pm
by bob_the _builder
This is for appointments.. If 30 days has passed since last appointment dispatch email, but exclude anyone that has another appointment booked between 29 days ago and into the future.. if that makes sence?

The code below sends out to anyone who had an appointment logged 30 days ago, even if they have made another appointment for after that..

Code: Select all

$sql = mysql_query("SELECT t.*, c.*
FROM timeslots as t
JOIN clients as c ON c.clientid = t.clientid
WHERE timeslot >= CURRENT_DATE - INTERVAL 31 DAY
AND timeslot  < CURRENT_DATE - INTERVAL 30 DAY");
Thanks

Re: Cant figure out query

Posted: Mon Oct 01, 2012 6:21 pm
by requinix
A simple query won't do this: there are three criteria in play affecting which results are being returned.
1. 30+ days since the last email
2. Appointments scheduled for 29 days ago or later
3. People matching criteria #1 but not #2
The first one controls the list you would normally be pulling from, the second one tells you about people who you don't want to send emails to, and the third makes sure that the second query is properly reflected in the first.
bob_the _builder wrote:If 30 days has passed since last appointment dispatch email
How would you tell that? I don't see anything in your query that seems to fit it.

Re: Cant figure out query

Posted: Mon Oct 01, 2012 7:21 pm
by bob_the _builder
An example of results of the above script (these people had an appointment 30 days ago, so would be emailed..)

Fri 31 Aug 2012 @ 8:30 am -> Alan
Fri 31 Aug 2012 @ 12:30 pm -> Martin
Fri 31 Aug 2012 @ 3:00 pm -> Craig
Fri 31 Aug 2012 @ 9:00 am -> Sue
Fri 31 Aug 2012 @ 2:00 pm -> Kellie
Fri 31 Aug 2012 @ 9:30 am -> Karen


But these 2 names allready have up coming appointments..

Thu 13 Sep 2012 @ 1:00 pm -> Kellie
Fri 14 Sep 2012 @ 3:30 pm -> Martin

So need to be excluded from the results above.. Basically looking for people that havnt been back in the last 30 days and havnt booked any further appointments.. A note saying we havnt seen you in awhile, if you would like to make another appointment, call.....

Re: Cant figure out query

Posted: Mon Oct 01, 2012 11:53 pm
by requinix
Performance aside, the simplest solution would be a NOT IN.

Code: Select all

SELECT fields FROM table WHERE conditions for the person to be in the list AND the person NOT IN (
    SELECT person FROM table WHERE conditions for the person to not be in the list
)
Pardon my pseudocode, this keyboard isn't that great for writing code.

Note how the outer query includes the fields you want but the inner query only grabs identifiers. As if you were writing

Code: Select all

WHERE person_id NOT IN (1, 2, 3)

Re: Cant figure out query

Posted: Wed Oct 03, 2012 4:38 pm
by bob_the _builder
The answer:

Code: Select all

$sql = mysql_query("SELECT c.clientid, c.email, c.name, MAX(DATE(t.timeslot)) as latest 
FROM timeslots as t
JOIN clients as c ON c.clientid = t.clientid
GROUP BY c.clientid
HAVING MAX(DATE(timeslot)) = CURDATE() - INTERVAL 30 DAY");
Maybe helpfull to someone else..