Home > Net >  Get a list of requests
Get a list of requests

Time:11-27

There are two tables, I need to get a list of client_id whose last requests (title) have not been processed. We believe that if there was a call after the request was created (created_datetime), then it has been processed.

tasks
client_id created_datetime title
calls
manager_id client_id call_datetime

I think that something needs to be done with time, but I don't understand what.

select client_id, max(calls.call_datetime)  - max(tasks.created_datetime) as time
from tasks join calls on tasks.client_id = calls.client_id 
group by client_id, call_datetime  

CodePudding user response:

with maxes as (select client_id, max(calls.call_datetime) latest_call
from calls 
group by client_id)
select tasks.client_id, title 
from tasks 
inner join maxes on maxes.client_id = tasks.client_id
where created_datetime > maxes.latest_call

If I understood correctly, this should be it

  • Related