Mathias Mathias - 4 months ago 10
SQL Question

MySQL - How to get all Customers who haven't been called in the last two hours

In my MySQL-db I have two tables

customer
and
call
.

Customer has two fields: id and name.

Call has the fields:

id
customer (NOT NULL FK)
dateOfCall (NOT NULL DATE)
timeOfCall (NOT NULL TIME).


There are Customers which haven't been called yet. A Customer can be called multiple times.

How do I make a query to get all customers who haven't been called in the last two hours?

SQL Fiddle with the schema and some minimal data: http://sqlfiddle.com/#!9/5c80f/1 (Bear in mind that the SQL Fiddle Server might be in another timezone than you!)

Answer

We need to check against the last time a person received a call. Here is the updated sql.

Select 
    cust.* 
from 
    `customer` cust 
left join 
(
    select
    customer
    from `call` cl 
    group by customer
    having 
    max(STR_TO_DATE(CONCAT(cl.dateOfCall, ' ', cl.timeOfCall), '%Y-%m-%d %H:%i:%s')) >= date_sub(NOW(), interval 2 hour)
) aa on cust.id = aa.customer
where aa.customer is null