Detect biggest date overlap entity-CodePudding

I want to find the maximum concurrent capacity of each instructor. If there is 1 day overlap between two course durations, then they are considered to be concurrent. I want the output as in column F.

Is there a way to achieve this output in Oracle SQL?

Script to create the data-

create table instructor_schedule(instructor_id varchar2(5), course_id varchar2(5), course_start_dt date, course_end_dt date);

insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C1', to_date('01-JAN-2022', 'DD-MON-YYYY'), to_date('30-JAN-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C2', to_date('25-DEC-2021', 'DD-MON-YYYY'), to_date('15-JAN-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C3', to_date('25-JAN-2022', 'DD-MON-YYYY'), to_date('05-FEB-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C4', to_date('26-JAN-2022', 'DD-MON-YYYY'), to_date('26-JAN-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I1', 'C5', to_date('01-MAR-2022', 'DD-MON-YYYY'), to_date('05-MAR-2022', 'DD-MON-YYYY'));

insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I2', 'C1', to_date('20-AUG-2022', 'DD-MON-YYYY'), to_date('22-AUG-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I2', 'C2', to_date('03-SEP-2022', 'DD-MON-YYYY'), to_date('04-SEP-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I2', 'C3', to_date('02-SEP-2022', 'DD-MON-YYYY'), to_date('02-SEP-2022', 'DD-MON-YYYY'));
insert into instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) values('I2', 'C4', to_date('01-SEP-2022', 'DD-MON-YYYY'), to_date('05-SEP-2022', 'DD-MON-YYYY'));

CodePudding user response：

You may use scalar subquery with the condition on intersecting intervals:

select
  m.*
  , (
    select count(*)
    from instructor_schedule lkp
    where m.instructor_id = lkp.instructor_id
      and m.course_id != lkp.course_id
      and m.course_start_dt <= lkp.course_end_dt
      and lkp.course_start_dt <= m.course_end_dt
  ) as intersects
from instructor_schedule m

INSTRUCTOR_ID	COURSE_ID	COURSE_START_DT	COURSE_END_DT	INTERSECTS
I1	C1	2022-01-01	2022-01-30	3
I1	C2	2021-12-25	2022-01-15	1
I1	C3	2022-01-25	2022-02-05	2
I1	C4	2022-01-26	2022-01-26	2
I1	C5	2022-03-01	2022-03-05	0
I2	C1	2022-08-20	2022-08-22	0
I2	C2	2022-09-03	2022-09-04	1
I2	C3	2022-09-02	2022-09-02	1
I2	C4	2022-09-01	2022-09-05	2

fiddle

UPD

Alternatively you may use a single table scan and model clause to perform calculations over multiple rows. cv below stands for current value of a specified dimension and brackets [...] are used to reference dimension values.

select /* gather_plan_statistics*/
  instructor_id,
  course_id, course_start_dt, course_end_dt,
  intersects,
  concurrent_courses,
  max_cap_flg
    
from instructor_schedule m
model
  partition by (instructor_id)
  dimension by (course_id, course_start_dt, course_end_dt)
  measures(
    0 as intersects,
    cast(null as varchar(1000)) as concurrent_courses,
    course_id as dummy,
    0 as max_cap_flg
  )
  rules update  sequential order(
    intersects[any, any, any]
      = count(intersects)[
          course_id != cv(course_id),
          course_start_dt <= cv(course_end_dt),
          course_end_dt >= cv(course_start_dt)
        ],
    concurrent_courses[any, any, any]
      = listagg(dummy, ',') within group(order by null)[
          course_id != cv(course_id),
          course_start_dt <= cv(course_end_dt),
          course_end_dt >= cv(course_start_dt)
        ],
    max_cap_flg[any,any,any]
      = case
          when intersects[cv(), cv(), cv()] = max(intersects)[any, any, any]
          then 1
        end
  )

INSTRUCTOR_ID	COURSE_ID	COURSE_START_DT	COURSE_END_DT	INTERSECTS	CONCURRENT_COURSES	MAX_CAP_FLG
I1	C1	2022-01-01	2022-01-30	3	C4,C3,C2	1
I1	C2	2021-12-25	2022-01-15	1	C1	null
I1	C3	2022-01-25	2022-02-05	2	C4,C1	null
I1	C4	2022-01-26	2022-01-26	2	C3,C1	null
I1	C5	2022-03-01	2022-03-05	0	null	null
I2	C1	2022-08-20	2022-08-22	0	null	null
I2	C2	2022-09-03	2022-09-04	1	C4	null
I2	C3	2022-09-02	2022-09-02	1	C4	null
I2	C4	2022-09-01	2022-09-05	2	C3,C2	1

fiddle

CodePudding user response：

You can use outer apply (in recent versions of Oracle anyway) to get the overlapping courses, and then aggregate those:

select i1.instructor_id, i1.course_id, i1.course_start_dt, i1.course_end_dt,
  coalesce(
    listagg(i3.course_id, ',') within group (order by i3.course_id),
    'None') as other_ids,
  count(i3.course_id) as other_count
from instructor_schedule i1
outer apply (
  select i2.course_id
  from instructor_schedule i2
  where i2.instructor_id = i1.instructor_id
  and i2.course_id != i1.course_id
  and not (i2.course_start_dt >= i1.course_end_dt
    or i2.course_end_dt <= i1.course_start_dt)
) i3
group by i1.instructor_id, i1.course_id, i1.course_start_dt, i1.course_end_dt
order by instructor_id, course_id

INSTRUCTOR_ID	COURSE_ID	COURSE_START_DT	COURSE_END_DT	OTHER_IDS	OTHER_COUNT
I1	C1	01-JAN-22	30-JAN-22	C2,C3,C4	3
I1	C2	25-DEC-21	15-JAN-22	C1	1
I1	C3	25-JAN-22	05-FEB-22	C1,C4	2
I1	C4	26-JAN-22	26-JAN-22	C1,C3	2
I1	C5	01-MAR-22	05-MAR-22	None	0
I2	C1	20-AUG-22	22-AUG-22	None	0
I2	C2	03-SEP-22	04-SEP-22	C4	1
I2	C3	02-SEP-22	02-SEP-22	C4	1
I2	C4	01-SEP-22	05-SEP-22	C2,C3	2

fiddle

This gets a slightly different result to what you've shown, but it looks like instructor I1's C3 and C4 should overlap with each other...

CodePudding user response：

Using just Analytic functions and CASE expressions...

SELECT INSTRUCTOR_ID, COURSE_ID, COURSE_START_DT, COURSE_END_DT,
       Nvl(OVERLAPING, 'None') "OVERLAPING", CNT
FROM(   SELECT DISTINCT
            i.INSTRUCTOR_ID, i.COURSE_ID, s.OVR, i.COURSE_START_DT, i.COURSE_END_DT,
            LISTAGG(s.COURSE_ID, ', ') WITHIN GROUP (Order By s.COURSE_ID) OVER(Partition By s.INSTRUCTOR_ID, s.OVR) "OVERLAPING",
            COUNT(DISTINCT s.COURSE_ID) OVER(Partition By s.INSTRUCTOR_ID, s.OVR) "CNT"
        FROM
            INSTRUCTOR_SCHEDULE i
        LEFT JOIN
            (
                Select 
                    s1.INSTRUCTOR_ID, s1.COURSE_ID, s1.COURSE_START_DT, s1.COURSE_END_DT,
                    CASE WHEN s1.COURSE_START_DT Between s2.COURSE_START_DT And s2.COURSE_END_DT OR s2.COURSE_START_DT Between s1.COURSE_START_DT And s1.COURSE_END_DT THEN s2.COURSE_ID END "OVR"
                From INSTRUCTOR_SCHEDULE s1
                Inner Join
                    INSTRUCTOR_SCHEDULE s2 ON(s2.INSTRUCTOR_ID = s1.INSTRUCTOR_ID And s2.COURSE_ID !=  s1.COURSE_ID)
                Where 
                    CASE WHEN s1.COURSE_START_DT Between s2.COURSE_START_DT And s2.COURSE_END_DT OR s2.COURSE_START_DT Between s1.COURSE_START_DT And s1.COURSE_END_DT THEN s2.COURSE_ID END Is Not Null
            ) s ON(s.INSTRUCTOR_ID = i.INSTRUCTOR_ID And s.OVR = i.COURSE_ID)
        ORDER BY i.INSTRUCTOR_ID, i.COURSE_ID
    )
/*  R e s u l t :
INSTRUCTOR_ID COURSE_ID COURSE_START_DT COURSE_END_DT OVERLAPING             CNT
------------- --------- --------------- ------------- --------------- ----------
I1            C1        01-JAN-22       30-JAN-22     C2, C3, C4               3 
I1            C2        25-DEC-21       15-JAN-22     C1                       1 
I1            C3        25-JAN-22       05-FEB-22     C1, C4                   2 
I1            C4        26-JAN-22       26-JAN-22     C1, C3                   2 
I1            C5        01-MAR-22       05-MAR-22     None                     0 
I2            C1        20-AUG-22       22-AUG-22     None                     0 
I2            C2        03-SEP-22       04-SEP-22     C4                       1 
I2            C3        02-SEP-22       02-SEP-22     C4                       1 
I2            C4        01-SEP-22       05-SEP-22     C2, C3                   2 
*/