Oracle SQL - Determine due date using a 2nd table-CodePudding

I am trying to figure out how to complete this task. I need to determine the due_date in the task_table based on the schedule in the duty_schedule table. There's not a consistent pattern of active days in the duty schedule, so I'm finding this to be difficult. I need to select the 5th active day in the duty_schedule table after the task_date. Only days marked 1 in the Active column should be used to determine the due date. I've included sample tables including expected results. I do not know how to approach this; if this can be done with a query or if a function needs to be created, or a better option?

TASK_TABLE: TASK_NUM TASK_DATE DUE_DATE

     1 15-SEP-21 null         
     2 19-SEP-21 null       
     3 20-SEP-21 null      
     4 22-SEP-21 null      
     5 25-SEP-21 null      
     6 26-SEP-21 null     
     7 28-SEP-21 null

duty_schedule: DAY_NUM DUTY_DATE ACTIVE

     1 15-SEP-21          1
     2 16-SEP-21          0
     3 17-SEP-21          1
     4 18-SEP-21          1
     5 19-SEP-21          0
     6 20-SEP-21          0
     7 21-SEP-21          1
     8 22-SEP-21          1
     9 23-SEP-21          1
    10 24-SEP-21          0
    11 25-SEP-21          1
    12 26-SEP-21          1
    13 27-SEP-21          0
    14 28-SEP-21          1
    15 29-SEP-21          1
    16 30-SEP-21          1
    17 01-OCT-21          1
    18 02-OCT-21          0
    19 03-OCT-21          1
    20 04-OCT-21          0
    21 05-OCT-21          1

TASK_TABLE Expected results: TASK_NUM TASK_DATE DUE_DATE

     1 15-SEP-21 23-SEP-21         
     2 19-SEP-21 26-SEP-21       
     3 20-SEP-21 26-SEP-21      
     4 22-SEP-21 29-SEP-21      
     5 25-SEP-21 01-OCT-21      
     6 26-SEP-21 03-OCT-21     
     7 28-SEP-21 05-OCT-21

Built sample tables using these:

CREATE TABLE TASK_TABLE (
task_num    number(2),
task_date   DATE,
due_date    DATE
);
    
INSERT INTO TASK_TABLE VALUES (1, '15-SEP-21', NULL);
INSERT INTO TASK_TABLE VALUES (2, '19-SEP-21', NULL);
INSERT INTO TASK_TABLE VALUES (3, '20-SEP-21', NULL);
INSERT INTO TASK_TABLE VALUES (4, '22-SEP-21', NULL);
INSERT INTO TASK_TABLE VALUES (5, '25-SEP-21', NULL);
INSERT INTO TASK_TABLE VALUES (6, '26-SEP-21', NULL);
INSERT INTO TASK_TABLE VALUES (7, '28-SEP-21', NULL);
COMMIT;

CREATE TABLE duty_schedule (
day_num NUMBER(3),
duty_date   DATE,
active      NUMBER(1)
);

INSERT INTO duty_schedule VALUES (1,'15-Sep-21',1);
INSERT INTO duty_schedule VALUES (2,'16-Sep-21',0);
INSERT INTO duty_schedule VALUES (3,'17-Sep-21',1);
INSERT INTO duty_schedule VALUES (4,'18-Sep-21',1);
INSERT INTO duty_schedule VALUES (5,'19-Sep-21',0);
INSERT INTO duty_schedule VALUES (6,'20-Sep-21',0);
INSERT INTO duty_schedule VALUES (7,'21-Sep-21',1);
INSERT INTO duty_schedule VALUES (8,'22-Sep-21',1);
INSERT INTO duty_schedule VALUES (9,'23-Sep-21',1);
INSERT INTO duty_schedule VALUES (10,'24-Sep-21',0);
INSERT INTO duty_schedule VALUES (11,'25-Sep-21',1);
INSERT INTO duty_schedule VALUES (12,'26-Sep-21',1);
INSERT INTO duty_schedule VALUES (13,'27-Sep-21',0);
INSERT INTO duty_schedule VALUES (14,'28-Sep-21',1);
INSERT INTO duty_schedule VALUES (15,'29-Sep-21',1);
INSERT INTO duty_schedule VALUES (16,'30-Sep-21',1);
INSERT INTO duty_schedule VALUES (17,'1-Oct-21',1);
INSERT INTO duty_schedule VALUES (18,'2-Oct-21',0);
INSERT INTO duty_schedule VALUES (19,'3-Oct-21',1);
INSERT INTO duty_schedule VALUES (20,'4-Oct-21',0);
INSERT INTO duty_schedule VALUES (21,'5-Oct-21',1);
COMMIT;

CodePudding user response：

Use a correlated subquery in the SELECT clause or in the FROM clause. Here I am using the FROM clause:

select
  t.task_num,
  t.task_date,
  d.duty_date
from task_table t
outer apply
(
  select ds.duty_date
  from duty_schedule ds
  where ds.active = 1
  and ds.duty_date > t.task_date
  order by ds.duty_date
  offset 4 rows
  fetch next row only
) d
order by t.task_num;

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=bdb05d7dfa12874ff03bb39f418524f0