CODEWITHSUNDEEP
sqloracle-database
user1009073
user1009073

Reputation: 3238

Oracle - multiple entries within a range question

In Oracle 12.2, I have a table which lists events...approx 1.3 M events...

  Event_Date  DATE,
  Person_ID Number,
  Person_Name VARCHAR2(100), 
  Event_Name_Tier1  VARCHAR2(100),
  ...

I need a query which says...

Show me all occurrences where a PERSON has a row for Event_Name_Tier1 = 'ABC' and that same person has another row (in same table) where Event_Name_Tier1 = (One of about 15 values, for example 'S2', 'T4', 'G7', 'Y9')) provided both records were within 90 days of each other.

How do I go about writing this?

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Given the nature of your question, I'm thinking something like:

select t.*
from t
where t.Event_Name_Tier1 = 'ABC' and
      exists (select 1
              from t t2
              where t2.person_id = t.person_id and
                    t2.Event_Name_Tier1 in ('S2', 'T4', 'G7', 'Y9', . . . ) and 
                    t2.event_date > t.event_date and
                    t2.event_date < t.event_date + interval '90' day
             );

Upvotes: 1

Related Questions