Create a Y/N flag column based of Overlapping date & time

I’m trying to create an overlap column that indicates if there has been any overlap of time on a specific day. The example is for one individual who has worked on several tasks/jobs throughout the day. I’m struggling to get the OVERLAP_FLAG column to populate correctly. I’m working on a 12C Oracle Database & I’ve tried LEAD,LAG & Partition with no success. In the example all the rows should be should have a calculated OVERLAP_FLAG of ‘Y’

enter image description here

CREATE "TEST1" (     "NAME" VARCHAR2(26 BYTE),      "JOB" VARCHAR2(26 BYTE),      "START_TIME" DATE,      "STOP_TIME" DATE,      "OVERLAP_FLAG" VARCHAR2(26 BYTE) );    Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','A02',to_date('18-AUG-2020 09.22.31','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.59.12','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','A01',to_date('18-AUG-2020 09.22.35','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.58.55','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','B01',to_date('18-AUG-2020 09.22.43','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.58.32','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','B02',to_date('18-AUG-2020 09.22.49','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.58.45','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','C02',to_date('18-AUG-2020 09.22.56','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.58.20','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','C01',to_date('18-AUG-2020 09.23.04','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.58.08','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','X01',to_date('18-AUG-2020 15.57.02','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.57.03','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','Y01',to_date('18-AUG-2020 15.57.11','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.57.12','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','Z01',to_date('18-AUG-2020 15.57.30','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.57.31','DD-MON-YYYY HH24.MI.SS'),'Y'); Insert into TEST1 (NAME,JOB,START_TIME,STOP_TIME,OVERLAP_FLAG) values ('JOE ','W01',to_date('18-AUG-2020 15.57.47','DD-MON-YYYY HH24.MI.SS'),to_date('18-AUG-2020 15.57.48','DD-MON-YYYY HH24.MI.SS'),'Y'); 
Add Comment
1 Answer(s)

One option uses exists and a case expression:

select     t.*,     case when exists (         select 1         from test1 t1         where              t1.name = t.name             and t1.job <> t.job             and t1.start_time < t.stop_time             and t1.stop_time  > t.start_time     ) then 'Y' else 'N' end overlap_flag from test1 t 

The predicates of the subquery describe a record with the same name, a different job, and an overlapping date range.

Demo on DB Fiddlde

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.