CODEWITHSUNDEEP
sqlpostgresqldate
Roger
Roger

Reputation: 657

Select only if no date are under 60 days from now

I want to make an SQL request to display a list of users but only the one who haven't accepted a mission since at least 60 days. A user have multiple user missions attached to him, so I need to look at all of them and display the user only if no missions have been accepted since 60 days.

Here is what I have so far, but it is wrong, the user is in the list even if he have accepted a mission less than 60 days ago, but the mission doesn't show up though. So this request just display every missions that have been accepted more than 60 days ago. That is not what I want.

SELECT
  u.username, u.id, u.email, date_part('days', now() - um.date_accepted) as "days since last mission"
FROM
  users_user u
INNER JOIN
  users_usermission um
ON
  u.id=um.user_id
WHERE
  date_part('days', now() - um.date_accepted) > 60

Upvotes: 2

Views: 1301

Answers (3)

GeekExplorer
GeekExplorer

Reputation: 260

This should give you some idea...

select workername, workerdoj, now()::date - workerdoj::date as days from workers where now()::date - workerdoj::date > 60

It returns all the exployees whose number of days from DOJ is > 60, you can modify it according to your scenario. Hope this helps

Upvotes: 0

jpw
jpw

Reputation: 44931

I think a query using a correlated not exists predicate would work:

SELECT 
    *
FROM
    users_user u
WHERE NOT EXISTS (
    SELECT 1 FROM  users_usermission um
    WHERE u.id = um.user_id  
      AND um.date_accepted > CURRENT_DATE - interval '60 days'
);

This would only return users for which there doesn't exists any missions within the last 60 days.

See this sample SQL Fiddle for an example.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You can do what you want by using aggregation and a left join. I would do it like this:

SELECT u.username, u.id, u.email,
       date_part('days', now() - um.date_accepted) as "days since last mission"
FROM users_user u LEFT JOIN
     (SELECT um.user_id, MAX(date_accepted) as maxda
      FROM users_usermission um
      GROUP BY um.user_id
     ) um
     ON u.id = um.user_id
WHERE maxda <= CURRENT_DATE - interval '60 days' or
      maxda IS NULL;

I'm not sure what the complicated calculation is in the SELECT clause, but I'm leaving it in. It doesn't appear to be part of your question.

Upvotes: 1

Related Questions