CODEWITHSUNDEEP
sqloracle-databasedate
dokgu
dokgu

Reputation: 6060

Oracle - return only those where the earliest date is present or future

Let's say I have an "opportunities" table (opportunities can have multiple deadlines) like so:

Opportunities

id    opp_id    deadline
---------------------------
1     40        20-Jan-2016
2     40        13-Jan-2016
3     40        20-Apr-2016
4     40        13-Apr-2016
5     73        29-Sep-2015
6     95        11-Dec-2016

Let's assume today's date is 15-Jan-2016, what should be returned is:

id    opp_id    deadline
---------------------------
6     95        11-Dec-2016

But if we assume that today's date is 16-Dec-2015, the rows I expect to receive are:

id    opp_id    deadline
---------------------------
1     40        20-Jan-2016
2     40        13-Jan-2016
3     40        20-Apr-2016
4     40        13-Apr-2016
6     95        11-Dec-2016

Basically the logic is to get those opportunities where all of the deadlines are either today or in the future. These are opportunities that I can still apply to because I can still meet the deadline.

How do I go about doing this?

EDIT

I guess I should have also clarified that if one of the deadlines for an opportunity has passed, I don't want to get the rest of the deadlines for that particular opportunity. Basically if the earliest deadline has passed, I don't want to retrieve anything for that opportunity.

Upvotes: 2

Views: 121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

One method uses analytic functions:

select o.*
from (select o.*, min(deadline) over (partition by opp_id) as min_deadline
      from opportunities o
     ) o
where min_deadline >= sysdate;

You could also do this with not exists;

select o.*
from opportunities o
where not exists (select 1
                  from opportunities o2
                  where o2.opp_id = o.opp_id and o2.deadline < sysdate
                 );

Upvotes: 5

Related Questions