Cant Figure Out Query

[bob_the _builder]

This is based on appointment time slots..

 

The following code grabs any clients who had an appointment 30 days ago..

 

$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");

 

But I also need to exclude anyone that has another appointment booked between 29 days ago and into the future.. if that makes sence?

 

An example of results from 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..

 

 

Thanks

[Barand]

So you are looking for those whose latest appointment was 30 days ago?

<?php
$sql = mysql_query("SELECT c.clientid
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;

[bob_the _builder]

Not exactly.. thats what my posted script does..

 

I need to pluck out anyone who had an appointment 30 days ago, but disregard anyone who had made an appointment within that 30 days or ay time in the future..

 

Its so an email can be dispatched to any client that hasnt been in for 30 days and havnt booked another appointment since then..

 

But let me check that.. I may have read your post wrong..

Edited October 2, 2012 by bob_the _builder

[bob_the _builder]

I run a test using my code and got one result of latest appointment being 30 days ago.. When I run you code I got no result.. I should have got back the same one result, as that is the latest appointment = 30 days ago.

 

I guess because the field is date/time.. and your code is taking the time stamp into consideration so looking for appointment exactly 30 days to the minute?

Edited October 2, 2012 by bob_the _builder

[Barand]

I run a test using my code and got one result of latest appointment being 30 days ago.. When I run you code I got no result.. I should have got back the same one result, as that is the latest appointment = 30 days ago.

 

I guess because the field is date/time.. and your code is taking the time stamp into consideration so looking for appointment exactly 30 days to the minute?

 

My code uses DATE() function so the time element of the datetime field is ignored. RTFM

Your dates given in the sample data are now 32 days old.

[Barand]

I ran my own test on a simple table.

 

timeslot test table
+----+---------------------+----------+
| id | timeslot		    | clientid |
+----+---------------------+----------+
|  9 | 2012-07-31 08:30:00 | Alan	 |
|  1 | 2012-08-31 08:30:00 | Alan	 |
|  3 | 2012-08-31 15:00:00 | Craig    |
|  6 | 2012-08-31 09:30:00 | Karen    |
|  5 | 2012-08-31 14:00:00 | Kellie   |
|  7 | 2012-09-13 13:00:00 | Kellie   |
| 10 | 2012-07-31 12:30:00 | Martin   |
|  2 | 2012-08-31 12:30:00 | Martin   |
|  8 | 2012-09-14 15:30:00 | Martin   |
|  4 | 2012-08-31 09:00:00 | Sue	  |
+----+---------------------+----------+

query

SELECT clientid, MAX(DATE(timeslot))
FROM timeslots as t
GROUP BY clientid
HAVING MAX(DATE(timeslot)) = CURDATE() - INTERVAL 32 DAY;

result

+----------+---------------------+
| clientid | MAX(DATE(timeslot)) |
+----------+---------------------+
| Alan	 | 2012-08-31		  |
| Craig    | 2012-08-31		  |
| Karen    | 2012-08-31		  |
| Sue	  | 2012-08-31		  |
+----------+---------------------+
Kellie and Martin excluded as required

[bob_the _builder]

I just run the query based on 15 days ago..

 

Tue 10 Jan 2012 @ 1:00 pm -> Geoff

Fri 20 Jan 2012 @ 12:00 pm -> Keith

Fri 24 Feb 2012 @ 11:30 am -> Greg

Mon 14 May 2012 @ 2:00 pm -> Darion

Wed 23 May 2012 @ 5:30 pm -> Eroll

Mon 03 Sep 2012 @ 2:00 pm -> Michelle

Wed 12 Sep 2012 @ 11:30 am -> Lisa

 

Some of these dates are going back months.. The idea is to pull out records that are exactly 15 days older than today, so they only get notifiyed once..

 

$sql = mysql_query("SELECT c.*, t.*
FROM timeslots as t
JOIN clients as c ON c.clientid = t.clientid
GROUP BY c.clientid
HAVING MAX(DATE(timeslot)) = CURDATE() - INTERVAL 15 DAY");

[Barand]

you don't have data 15 days old

 

mysql> SELECT CURDATE() - INTERVAL 15 DAY;
+-----------------------------+
| CURDATE() - INTERVAL 15 DAY |
+-----------------------------+
| 2012-09-18				  |
+-----------------------------+

[bob_the _builder]

It looks like it is briging back their first appointment ever..

 

Should it not bring back any results at all, seems like a random result..

 

If I do 32 days ago I get:

 

Fri 01 Jun 2012 @ 4:00 pm -> Josh

Thu 26 Jul 2012 @ 11:00 am -> Chris

 

Again, a match that doesnt fall within 32 days old.. Lost on this one.

Edited October 3, 2012 by bob_the _builder

[Barand]

Thats because you SELECT * with a GROUP BY so it pulls the date from the first row for each clientid. (Don't SELECT * )

 

SELECT the columns you need. I have no idea what they are because of the *.

 

I thought you were trying to find clients who met the criteria.

[bob_the _builder]

Ops I should have looked harder.. It does appear to be bringing back the correct clientid's.. but listing the timeslot field as DESC..

 

The reason for SELECT * is so within the query loop, taking firstname, email, timeslot etc and dispatch an email to those clients.. The date field needs to be the latest (last) appointment date/time so we can say your last visit was on ####.

 

Can the query be altered to bring the latest timeslot as the result?

 

 

Thanks

[fenway]

You'll need to join the max timeslot back to the original table.

[Barand]

$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");

[bob_the _builder]

Thanks for all you help Barand! Just one more thing..

 

MAX(DATETIME(t.timeslot)) as latest

 

Should that not show the date and time printing when $latest?

[Barand]

MAX(DATETIME(t.timeslot)) as latest

 

Should that not show the date and time printing when $latest?

 

I would have expected that to give unknown function error.

 

DATE(timeslot) extracts date portion

TIME(timeslot) extracts time portion

timeslot gives both

 

So you want MAX(timeslot) for latest date+time