Oracle YEAR logic

Question

I have 2 tables. First table TABLE1 has 3 columns (ROLLNO, CODE, ADM_DT).

CREATE TABLE TABLE1 
( ROLLNO VARCHAR2(3) NOT NULL, 
  CODE VARCHAR2(3) NOT NULL, 
  ADM_DT TIMESTAMP(3) NOT NULL
);

Second table TABLE2 has 3 columns (CODE, YEAR).

CREATE TABLE TABLE2
(
  CODE VARCHAR2(3) NOT NULL,
  YEAR VARCHAR2(4) NOT NULL
 );

---

TABLE1 has few records mentioned below:

INSERT INTO TABLE1(ROLLNO, CODE, ADM_DT) VALUES ('100','ABC', '12-NOV-21 12.00.00.000000000 AM');
INSERT INTO TABLE1(ROLLNO, CODE, ADM_DT) VALUES ('101','ACD', '12-DEC-21 12.00.00.000000000 AM');
INSERT INTO TABLE1(ROLLNO, CODE, ADM_DT) VALUES ('102','ABD', '15-JAN-21 12.00.00.000000000 AM');
INSERT INTO TABLE1(ROLLNO, CODE, ADM_DT) VALUES ('103','DEF', '14-AUG-21 12.00.00.000000000 AM');
INSERT INTO TABLE1(ROLLNO, CODE, ADM_DT) VALUES ('104','DFE', '17-JUL-21 12.00.00.000000000 AM');
INSERT INTO TABLE1(ROLLNO, CODE, ADM_DT) VALUES ('105','FED', '21-SEP-21 12.00.00.000000000 AM');

ROLLNO   CODE   ADM_DT
-----------------------------------
100      ABC    12-NOV-21 12.00.00.000000000 AM
101      ACD    12-DEC-21 12.00.00.000000000 AM
102      ABD    15-JAN-21 12.00.00.000000000 AM
103      DEF    14-AUG-21 12.00.00.000000000 AM
104      DFE    17-JUL-21 12.00.00.000000000 AM
105      FED    21-SEP-21 12.00.00.000000000 AM

---

TABLE2 has few records mentioned below:

INSERT INTO TABLE2(CODE, YEAR) VALUES ('ABC','2022');
INSERT INTO TABLE2(CODE, YEAR) VALUES ('ADC','2022');
INSERT INTO TABLE2(CODE, YEAR) VALUES ('DEF','2021');
INSERT INTO TABLE2(CODE, YEAR) VALUES ('DFE','2021');   

CODE   YEAR
-------------
 ABC   2022
 ADC   2022
 DEF   2021
 DFE   2021

---

I need to select the records from TABLE1 in such a way that

  1) fetch the month present in ADM_DT value and if month is between Sep and Dec and also the year, then check the CODE value present in YEAR+1 of TABLE2, if present then don't select the record from TABLE1 else select.
 
  Ex: Let us take a record of ROLLNO = '102' and CODE is 'ABC' and it has ADM_DT as '12-NOV-21 12.00.00.00.000000000 AM' so here month is November and the year is 2021, so check whether CODE 'ABC' present in TABLE2 with YEAR '2022'...code 'ABC' present in TABLE2, then don't select this record from TABLE1.

  2) fetch the month present in ADM_DT value and if month is between Jan and Aug, then check the CODE value present in YEAR of TABLE2, if present then don't select the record from TABLE1 else select.

  Ex: Let us take a record of ROLLNO = '103' and CODE is 'DEF' and it has ADM_DT as '14-AUG-21 12.00.00.00.000000000 AM' so here month is August and the year is 2021 so check whether CODE 'DEF' present in TABLE2 with YEAR '2021'...code 'DEF' present in TABLE2, then don't select this record from TABLE1.

---

This is the Result table I need:

 ROLLNO   CODE      ADM_DT
 ------------------------------------------------
 101      ACD    12-DEC-21 12.00.00.00.000000000 AM
 102      ABD    15-JAN-21 12.00.00.00.000000000 AM 
 105      FED    21-SEP-21 12.00.00.00.000000000 AM

This is the Result table I am getting:

 ROLLNO   CODE      ADM_DT
 ------------------------------------------------
 100      ABC    12-NOV-21 12.00.00.000000000 AM (This shouldn't get picked up as this record has Month as Nov and year as 2021, so we need to go to the TABLE2 and check whether we have CODE 'ABC' in the YEAR '2022', its present so it shouldn't pick up)
 101      ACD    12-DEC-21 12.00.00.000000000 AM
 102      ABD    15-JAN-21 12.00.00.000000000 AM 
 105      FED    21-SEP-21 12.00.00.000000000 AM

This is the query I have tried:

SELECT * FROM TABLE1 T1
WHERE NOT EXISTS (
                    SELECT 1 FROM TABLE2 T2
                    WHERE T1.CODE = T2.CODE
                    AND EXTRACT(YEAR FROM T1.ADM_DT) = 
                        CASE
                         WHEN EXTRACT(MONTH FROM(T1.ADM_DT)) BETWEEN 9 AND 12 THEN TO_NUMBER(T2.YEAR)+1
                         WHEN EXTRACT(MONTH FROM(T1.ADM_DT)) BETWEEN 1 AND 8 THEN TO_NUMBER(T2.YEAR)
                        END
                 );

Is this correct query? because I have executed this query and it gave me wrong result...might be issue with YEAR logic I have written in query (Need to use NOT EXISTS query).

Answer 1

You can create a range from the table2.year rather than trying to match the years based on the month in table1.adm_dt. This has the added bonus that Oracle can use an index on the table1.adm_dt column whereas your query would not use an index and would need separate function-based indexes for each of the EXTRACT functions for MONTH and YEAR.

Either:

SELECT t1.*
FROM   table1 T1
       LEFT OUTER JOIN table2 T2
       ON (t1.code = t2.code
          AND t1.adm_dt >= TO_DATE((t2.year - 1) || '-09-01', 'YYYY-MM-DD')
          AND t1.adm_dt <  TO_DATE(t2.year || '-09-01', 'YYYY-MM-DD')
          )
WHERE  t2.code IS NULL;

or:

SELECT *
FROM   table1 T1
WHERE  NOT EXISTS (
         SELECT 1
         FROM   table2 T2
         WHERE  t1.code = t2.code
         AND    t1.adm_dt >= TO_DATE((t2.year - 1) || '-09-01', 'YYYY-MM-DD')
         AND    t1.adm_dt <  TO_DATE(t2.year || '-09-01', 'YYYY-MM-DD')
       );

Which, for your sample data, both output:

ROLLNO	CODE	ADM_DT
102	ABD	15-JAN-21 00.00.00.000
105	FED	21-SEP-21 00.00.00.000
101	ACD	12-DEC-21 00.00.00.000

db<>fiddle here