I have 4 source tables:

CREATE TABLE TABLE1_NAMES (NAMES VARCHAR2(12)); 

INSERT INTO TABLE1_NAMES VALUES ('JOHN');
INSERT INTO TABLE1_NAMES VALUES ('RYAN');
INSERT INTO TABLE1_NAMES VALUES ('OLAN');
INSERT INTO TABLE1_NAMES VALUES ('TONY');

CREATE TABLE TABLE2_LINES (NAMES VARCHAR2(12), LINE_NO NUMBER, UNITS NUMBER(18,4), CODE VARCHAR2(4));

INSERT INTO TABLE2_LINES VALUES ('JOHN',1,1,'101');
INSERT INTO TABLE2_LINES VALUES ('JOHN',2,1,'202');
INSERT INTO TABLE2_LINES VALUES ('JOHN',3,1,'180');
INSERT INTO TABLE2_LINES VALUES ('JOHN',4,2,'300');

INSERT INTO TABLE2_LINES VALUES ('RYAN',1,2,'180');
INSERT INTO TABLE2_LINES VALUES ('RYAN',2,1,'180');
INSERT INTO TABLE2_LINES VALUES ('RYAN',3,1,'500');

INSERT INTO TABLE2_LINES VALUES ('OLAN',1,1,'301');

INSERT INTO TABLE2_LINES VALUES ('TONY',1,1,'201');

CREATE TABLE TABLE3_DATES (NAMES VARCHAR2(12), FR_DT TIMESTAMP(3), TO_DT TIMESTAMP(3), START_DT TIMESTAMP(3),END_DT TIMESTAMP(3));

INSERT INTO TABLE3_DATES VALUES ('JOHN','01-DEC-22 12.00.00.000000000 AM','05-DEC-22 12.00.00.000000000 AM','03-DEC-22 12.00.00.000000000 AM','09-DEC-22 12.00.00.000000000 AM');
INSERT INTO TABLE3_DATES VALUES ('RYAN','01-DEC-22 12.00.00.000000000 AM','04-DEC-22 12.00.00.000000000 AM','03-DEC-22 12.00.00.000000000 AM','09-DEC-22 12.00.00.000000000 AM');
INSERT INTO TABLE3_DATES VALUES ('OLAN','01-DEC-22 12.00.00.000000000 AM','05-DEC-22 12.00.00.000000000 AM','03-DEC-22 12.00.00.000000000 AM','09-DEC-22 12.00.00.000000000 AM');

 INSERT INTO TABLE3_DATES VALUES ('TONY','01-DEC-22 12.00.00.000000000 AM','05-DEC-22 12.00.00.000000000 AM','03-DEC-22 12.00.00.000000000 AM','09-DEC-22 12.00.00.000000000 AM');

CREATE TABLE TABLE4_CODES (CD_NM VARCHAR2(12), B_CODE VARCHAR2(4), E_CODE VARCHAR2(4));
 
INSERT INTO TABLE4_CODES VALUESS ('CODELIST','100','101');
INSERT INTO TABLE4_CODES VALUESS ('CODELIST','180','180');
INSERT INTO TABLE4_CODES VALUESS ('CODELIST','200','219');
COMMIT;

I have 2 result tables : RESULT1_CALC, RESULT2_FINAL (one table is for calculation of total units per person and other table is the final result table which stores the person name with code)

CREATE TABLE RESULT1_CALC (NAMES VARCHAR2(12), FR_DT TIMESTAMP(3), TO_DT TIMESTAMP(3), START_DT TIMESTAMP(3), END_DT TIMESTAMP(3), TOT_UNITS NUMBER);

CREATE TABLE RESULT2_FINAL (NAMES VARCHAR2(12), EX_CD VARCHAR2(3));

Logic Explanation:

We have TABLE2_LINES data where we calculate SUM(UNITS) per person based on CODE value present in TABLE4_CODES in such a way that

1. If 180 code only is present for a person then calculate SUM(UNITS) and compare with formula TO_DT - FR_DT, if not equal insert record (person name and 'ABC' code) into RESULT2_FINAL table
2. If 180 code is present along with other eligible codes (here eligible code is nothing but code present in TABLE4_CODES) for a person then calculate SUM(UNITS) and compare with formula END_DT - START_DT, if not equal insert record (person name and 'ABC' code) into RESULT2_FINAL table
3. If 180 code is not present but other eligible codes are present(here eligible code is nothing but code present in TABLE4_CODES) for a person then calculate SUM(UNITS) and compare with formula END_DT - START_DT, if not equal insert record (person name and 'ABC' code) into RESULT2_FINAL table

Example:

• Case 1: For person JOHN, We have eligible codes i.e. 101,202, 180 (these codes present in TABLE4_CODES table) ..It has other eligible codes along with 180 so SUM(UNITS) is 3 and this value is compared with formula TO_DT - FR_DT i.e. December 5 - December 1 which is 4 (not equal to SUM(UNITS)) so insert into RESULT2_FINAL table.

• Case 2: For person RYAN, We have eligible code i.e. 180 only (this code present in TABLE4_CODES table) ... so SUM(UNITS) is 3 and this value is compared with formula END_DT - START_DT i.e. December 9 - December 3 which is 6 (not equal to SUM(UNITS)) so insert into RESULT2_FINAL table.

• Case 3: For person OLAN, we don't have eligible codes so need to do anything.

• Case 4: For person TONY, we have eligible code 201 but we don't have 180 so (this code present in TABLE4_CODES table) ... so SUM(UNITS) is 1 and this value is compared with formula END_DT - START_DT i.e. December 9 - December 3 which is 6 (not equal to SUM(UNITS)) so insert into RESULT2_FINAL table.

But in SQL query which I have mentioned below, it is not inserting person RYAN and also TONY records into RESULT2_FINAL table as I am not sure how to write query to differ the above 2 cases with calculation to get expected result.

INSERT INTO RESULT1_CALC 
(
  SELECT T1.NAMES, T3.FR_DT, T3.TO_DT, T3.START_DT, T3.END_DT, RES.TOT_UNITS
  FROM TABLE1_NAMES T1
       JOIN (
         SELECT T2.NAMES, SUM(T2.UNITS), TOT_UNITS
         FROM TABLE2_LINES T2
         JOIN TABLE4_CODES T4
         ON T4.CD_NM = 'CODELIST'
         AND T2.CODE BETWEEN T4.B_CODE AND T4.E_CODE
         GROUP BY T2.NAMES 
       ) RES
       ON T1.NAMES = RES.NAMES
       JOIN TABLE3_DATES T3
       ON T1.NAMES = T3.NAMES
);
COMMIT;

INSERT INTO RESULT2_FINAL 
(
  SELECT DISTINCT R1.NAMES, 'ABC' EX_CD
  FROM RESULT1_CALC R1
  WHERE R1.TOT_UNITS <> (EXTRACT(DAY FROM TO_DT - FR_DT))
);
COMMIT;

Output at the end:

• RESULT2_FINAL table should be populated with the help of RESULT1_CALC table.

CodePudding user response：

You don't need RESULT1_CALC and can just insert the rows directly using the source tables into the final table:

INSERT INTO RESULT2_FINAL (names, ex_cd)
  SELECT l.names, 'ABC'
  FROM   (
           SELECT names,
                  SUM(units) AS total_units,
                  COUNT(CASE WHEN l.code = '180' THEN 1 END) AS has180,
                  COUNT(CASE WHEN l.code = '180' THEN NULL ELSE 1 END) AS hasOther
           FROM   TABLE4_CODES c
                  INNER JOIN TABLE2_LINES l
                  ON TO_NUMBER(l.code) BETWEEN TO_NUMBER(c.b_code) AND TO_NUMBER(c.e_code)
           WHERE  c.cd_nm = 'CODELIST'
           GROUP BY names
         ) l
         INNER JOIN TABLE3_DATES d
         ON l.names =  d.names
  WHERE  (   l.has180 > 0 AND l.hasOther = 0
         AND NUMTODSINTERVAL(l.total_units, 'DAY') = d.to_dt - d.fr_dt
         )
  OR     (   l.has180 > 0 AND l.hasOther > 0
         AND  NUMTODSINTERVAL(l.total_units, 'DAY') != d.end_dt - d.start_dt
         );

Which, for the sample data, inserts the values:

fiddle