Mysql selects all records where the datetime field is less than the specified value

Question

So, I'm trying to run a query that will select, in this case, clients who did not have an appointment, this is X amount of time (for example, 2 weeks). It comes down to "showing me a list of clients who did not have a meeting in two weeks." I am trying to solve this in php by doing something like:

$date = new DateTime; $ago = new DateInterval('P2W'); $target = $date->sub($ago); //query to select clients that aren't scheduled after the $target date $clients = ...; 

Two tables are included: appt_tbl and clients_tbl . appt_tbl stores client_id for each record of the record.

So, essentially, I need to select the “maximum” destination for each client, and if it is <my $ target date, include them in the query results. I tried various query options, queries with subqueries, but I came across the correctness of the query.

My current attempt looks something like this:

 SELECT * FROM clients_tbl INNER JOIN ( SELECT client_id FROM appt_tbl WHERE MAX(appt_date_time) < '2012-07-22' GROUP BY client_id ) appts ON appts.client_id = clients_tbl.client_id; 

This should also include clients who have never been scheduled (IE will not appear in appt_tbl), but not clients who have an appointment over the next two weeks.

Answer 1

 SELECT a.* FROM clients_tbl a LEFT JOIN appt_tbl b ON a.client_id = b.client_id AND b.appt_date_time >= CURDATE() - INTERVAL 2 WEEK WHERE b.client_id IS NULL 

What this request does first (before the WHERE filter) is that all clients are selected, regardless of whether they plan to schedule an appointment more than two weeks ago.

If the client does not have a meeting more than two weeks ago, the values ​​in the joined table will be NULL . We need all rows in which the join conditions were not satisfied (i.e., the values ​​in the joined table are zero), which is done using WHERE b.client_id IS NULL .

This also includes end customers who do not have the appropriate appointments at all.

Future nominated customers are excluded.

There is also no need to build a datetime string in PHP. You can simply do this directly in the request (although you can pass a few weeks ago as a parameter).

Answer 2

It can be quite simple, I think.

 select users from time_sheet where created_date >= date('2013-12-01'); 

In your case, you must do so. Instead of this

 WHERE MAX(appt_date_time) < '2012-07-22' 

do it

 WHERE MAX(appt_date_time) < date('2012-07-22') 

What the date comparison actually does was earlier a date with string matching.

Hurrah!!