Generating one row for each employee/date combination, then excluding those where an employee is already assigned to another project, and then recombining the remaining rows into ranges, has the advantage of simplicity, and would work even if the inputs aren't "normalized" (by which I mean that the pre-existing, already-assigned ranges for each employee are not overlapping). However, this approach doesn't scale well.
An alternative approach is to work directly with ranges. Set operations (union, intersection, complement) defined on finite disjoint unions of intervals can be implemented directly in SQL, for example using analytic functions - as your intuition suggested.
One common question is, given a finite union of possibly overlapping intervals, write it as a disjoint union of non-adjacent intervals. (This includes stitching together intervals like 1-JAN to 13-JAN and 14-JAN to 23-JAN into a single interval, 1-JAN to 23-JAN. In the extreme, it includes creating a single range out of a set of consecutive, single dates.) This is known as "merging" intervals, and has a very nice solution using MATCH_RECOGNIZE.
In your problem, it seems that it is already assumed that the existing assignments for each employee form a disjoint union. This simplifies the solution to the question you posed; otherwise, the first step would be to "normalize" the existing assignments, before looking for availability for a new project.
You said the new project has the same start and end dates as the existing one. That doesn't seem to be relevant to the problem. Rather, the input should be a table of existing assignments (to possibly more than one project!) and a new project with given start and end dates. You must find availability for every employee, for the span of the new project.
In the code below I re-create the input tables. You need also an "employees" table showing all the available employees. In my sample, employee '444' is not currently assigned to any project; if you only look at table "empprojects", you would never know that this employee even existed.
In the sample, projects P1000, P1002, P1003 and P1004 are pre-existing, and I show a few employees assigned to these projects. P1001 is the new project. Notice also a few special cases I included: employee '002' is already assigned for the entire duration of project P1001, so he won't be included in the output. Employee '444' is available for the entire duration. And employee '003' is available for just one day in a few cases - and notice also what happens when he is assigned from one project to another on adjacent dates (there is no slack there for him to work on P1001). You always need to include such special cases for thorough testing. (It is very likely that I still missed a few other possible special situations.)
Importantly, note that the reference to project P1001 is a red herring. Really what the query does is to find available employee/date combos for a given date range (interval), in this case between 1 JAN 2023 and 31 DEC 2023. It doesn't matter what that range represents.
drop table projectsm purge;
drop table empprojects purge;
create table empm (
empno varchar2(30),
ename varchar2(40) );
create table projectsm (
project_number varchar2(30),
start_date date,
end_date date );
create table empprojects (
empno varchar2(30),
project_number varchar2(100),
project_in_date date,
project_out_date date );
insert into empm values ('001', 'Emp 001');
insert into empm values ('002', 'Emp 002');
insert into empm values ('003', 'Emp 003');
insert into empm values ('444', 'Emp 444');
-- always do this if you give dates as strings:
alter session set nls_date_format='dd-MON-yyyy';
insert into projectsm values ('P1000','01-JAN-2023','31-DEC-2023');
insert into projectsm values ('P1001','01-JAN-2023','31-DEC-2023');
insert into projectsm values ('P1002','01-MAR-2023','31-OCT-2023');
insert into projectsm values ('P1003','01-JAN-2022','31-DEC-2024');
insert into projectsm values ('P1004','15-APR-2023','20-JAN-2024');
insert into empprojects values ('001','P1000','15-JAN-2023','06-JUN-2023');
insert into empprojects values ('001','P1000','30-JUN-2023','17-JUL-2023');
insert into empprojects values ('002','P1003','15-JAN-2022','31-DEC-2023');
insert into empprojects values ('003','P1000','02-JAN-2023','29-APR-2023');
insert into empprojects values ('003','P1000','01-MAY-2023','14-MAY-2023');
insert into empprojects values ('003','P1002','15-MAY-2023','06-JUN-2023');
insert into empprojects values ('003','P1004','30-AUG-2023','30-DEC-2023');
commit;
with
proj_dates (start_date, end_date) as (
select start_date, end_date
from projectsm
where project_number = 'P1001'
)
, prep (empno, project_in_date, project_out_date) as (
select empno, null, start_date - 1
from empm cross join proj_dates
union all
select empno, project_in_date, project_out_date
from empprojects
where project_out_date >= (select start_date from proj_dates)
and
project_in_date <= (select end_date from proj_dates)
)
, comp (empno, project_in_date, project_out_date) as (
select empno,
project_out_date + 1,
lead(project_in_date - 1, 1, (select end_date from proj_dates))
over (partition by empno order by project_out_date)
from prep
)
select empno, project_in_date, project_out_date
from comp
where project_in_date <= project_out_date
order by empno, project_in_date
;
EMPNO PROJECT_IN_DATE PROJECT_OUT_DATE
----- ---------------- ----------------
001 01-JAN-2023 14-JAN-2023
001 07-JUN-2023 29-JUN-2023
001 18-JUL-2023 31-DEC-2023
003 01-JAN-2023 01-JAN-2023
003 30-APR-2023 30-APR-2023
003 07-JUN-2023 29-AUG-2023
003 31-DEC-2023 31-DEC-2023
444 01-JAN-2023 31-DEC-2023