How to pull the Primary key information from a table missing dates from a give date range in Oracle

I am new to SQL and trying to work on SQL that can help in pulling the records from a table that has missing date range. Below are the details.

CREATE TABLE a
(
job_id   INT NOT NULL PRIMARY KEY,
DATE     DATE NOT NULL
);

INSERT INTO a VALUES
(1, '2024-01-01'),
(1, '2024-02-01'),
(1, '2024-03-01'),
(1, '2024-05-01'),
(1, '2024-06-01'),
(1, '2024-07-01'),
(1, '2024-09-01'),
(2, '2024-01-01'),
(2, '2024-02-01'),
(2, '2024-03-01'),
(2, '2024-04-01'),
(2, '2024-06-01')

The date range I provided is 01-JAN-2024 – 10-JAN-2024 and the result I am expecting is:

JOB_ID | DATE

1     |  2024-04-01

1     |  2024-08-01

1     |  2024-10-01

2     |  2024-05-01

2     |  2024-07-01

2     |  2024-08-01

2     |  2024-09-01

2     |  2024-10-01

You need to sign in to view this answers

Related Post