ORACLE SQL - How to find the number of reliefs each teacher has, each day, 2 months before the teacher resigned?

Question

I need some help in finding the number of reliefs each teacher has, every single day, 2 months before the teacher resigns. Join_dt - teacher's join date, Resign_dt - teacher's resign date, Relief_ID - Relief teacher's ID, Start_dt - Relief's start date, End_dt - Relief's end date,

note that there may be overlapping dates between 2 or more different reliefs and so I need to find the number of distinct reliefs each teacher has for each date.

This is what I am given:

Teacher_ID  Join_dt     Resign_dt   Relief_ID  Start_dt    End_dt      
12          2006-08-30  2019-08-01  20         2017-02-07  2019-07-04      
12          2006-08-30  2019-08-01  20         2016-11-10  2019-01-30      
12          2006-08-30  2019-08-01  103        2016-08-20  2019-07-29      
12          2006-08-30  2019-08-01  17         2016-01-30  2017-12-30      
23          2017-10-01  2018-11-12  44         2018-10-19  2018-11-11      
23          2017-10-01  2018-11-12  29         2018-04-01  2018-12-02      
23          2017-10-01  2018-11-12  06         2017-11-25  2018-05-02      
05          2015-02-11  2019-10-02  38         2019-01-17  2019-07-21      
05          2015-02-11  2019-10-02  11         2018-11-02  2019-02-05      
05          2015-02-11  2019-10-02  15         2018-09-30  2018-10-03 

Expected result:

Teacher_ID Dates       No_of_reliefs
12         2019-07-31  0
12         2019-07-30  0
12         2019-07-29  1
12         2019-07-28  1
12         2019-07-27  1
...        ...
12         2019-07-04  2
...        ...
12         2016-05-30  2
12         2016-05-29  2
12         2016-05-28  2
12         2016-05-27  2
12         2016-05-26  1 
23         2018-10-31  2
...        ...

For date 2019-07-29, No_of_reliefs = 1 because of Relief_ID 103. For date 2017-07-04, No_of_reliefs = 2 because of Relief_ID 20 & 103.

Dates are supposed to start from 1 month before the teacher resigned. For Teacher_ID 23, since she resigned on 2019-11-12, dates shall start from 2019-10-31.

I have tried using connect by but the execution time is really long since it involves a large amount of data. Any other methods will be greatly appreciated!! Thank you kind souls!!!

Answer 1

You can use

connect by level <= last_day(add_months(Resign_dt,-1)) - add_months(Resign_dt,-2) clause :

I suppose you mean 2 months before resignment for the starting date, and ending on the last day of the previous month.

with t1(Teacher_ID,Resign_dt,Relief_ID,start_dt,end_dt) as
(
  select 12,date'2019-08-01',20 ,date'2017-02-07',date'2019-07-04' from dual union all      
  select 12,date'2019-08-01',20 ,date'2016-11-10',date'2019-01-30' from dual union all
  select 12,date'2019-08-01',103,date'2016-08-20',date'2019-07-29' from dual
 ......
), t2 as
(
 select distinct last_day(add_months(Resign_dt,-1)) - level + 1 as Resign_dt, Teacher_ID
   from t1
connect by level <= last_day(add_months(Resign_dt,-1)) - add_months(Resign_dt,-2)                         
    and prior Teacher_ID = Teacher_ID and prior sys_guid() is not null
)
select Teacher_ID, to_char(Resign_dt,'yyyy-mm-dd') as Dates,
       (select count(distinct Relief_ID) 
          from t1 
         where t2.Resign_dt between start_dt and end_dt
           and t2.Teacher_ID = Teacher_ID
        )
  from t2
 order by Teacher_ID, Resign_dt desc;

Demo