oracle query to identify some condition based overlapping date periods

I have a data set with overlapping dates in a slowly changing dimension table. I am trying to come up with a query to find out the persons with overlapping date ranges based on few conditions.

for example

PERSON_ID,RELATION_ID,RELATION_NAME,START_DT,  END_DT, ACTIVE_IND,LATEST_ROW
STEVE     -1          NONE          7/26/2018  10/24/2018 Y          N
STEVE     111         STUDENT       8/8/2018   10/22/2018 Y          N
STEVE     111         STUDENT       10/23/2018 12/31/4712 Y          Y
STEVE     -1          NONE          10/25/2018 2/7/2019   Y          N
STEVE     222         Manager       8/9/2018   12/31/4712 Y          Y
BOB       333         ASSOCIATE     8/9/2018   12/31/4712 Y          Y
BOB       666         TEACHER       8/10/2018  12/31/4712 Y          Y
Dave      555         Director      8/9/2018   12/31/4712 Y          Y

I want to find the person_ids with overlapping date ranges only when atleast one of the relationship_id is -1. for example, BOB is both associate and teacher for overlapping periods. its ok to have 2 roles for a person. However, i want to find out all persons with overlapping dates which involves "None" relationship and some other role. Example: steve.

Could you please give me some suggestions as to how to come up with a query

Solution

Oracle Setup:

CREATE TABLE table_name ( PERSON_ID, RELATION_ID, RELATION_NAME, START_DT, END_DT, ACTIVE_IND,LATEST_ROW ) AS
SELECT 'STEVE',  -1, 'NONE',      DATE '2018-07-26', DATE '2018-10-24', 'Y', 'N' FROM DUAL UNION ALL
SELECT 'STEVE', 111, 'STUDENT',   DATE '2018-08-08', DATE '2018-10-22', 'Y', 'N' FROM DUAL UNION ALL
SELECT 'STEVE', 111, 'STUDENT',   DATE '2018-10-23', DATE '4712-12-31', 'Y', 'Y' FROM DUAL UNION ALL
SELECT 'STEVE',  -1, 'NONE',      DATE '2018-10-25', DATE '2019-02-07', 'Y', 'N' FROM DUAL UNION ALL
SELECT 'STEVE', 222, 'Manager',   DATE '2018-08-09', DATE '4712-12-31', 'Y', 'Y' FROM DUAL UNION ALL
SELECT 'BOB',   333, 'ASSOCIATE', DATE '2018-08-09', DATE '4712-12-31', 'Y', 'Y' FROM DUAL UNION ALL
SELECT 'BOB',   666, 'TEACHER',   DATE '2018-08-10', DATE '4712-12-31', 'Y', 'Y' FROM DUAL UNION ALL
SELECT 'Dave',  555, 'Director',  DATE '2018-08-09', DATE '4712-12-31', 'Y', 'Y' FROM DUAL;

Query:

SELECT DISTINCT
       PERSON_ID
FROM   table_name t
WHERE  RELATION_ID = -1
AND    EXISTS (
  SELECT 1
  FROM   table_name o
  WHERE  t.person_id = o.person_id
  AND    t.end_dt    > o.start_dt
  AND    t.start_dt  < o.end_dt
  AND    o.relation_id <> -1
)

Output:

| PERSON_ID |
| :-------- |
| STEVE     |

db<>fiddle here