Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table projects( projectno, stages, startdate, completiondate )
as
select 'PROJ_001',	1, date '2021-03-12', date '2021-03-12' from dual union all
select 'PROJ_001',	2, date '2021-03-14', date '2021-03-14' from dual union all
select 'PROJ_001',	3, date '2021-03-15', date '2021-03-15' from dual union all
select 'PROJ_001',	4, date '2021-03-18', date '2021-03-18' from dual union all
select 'PROJ_002',	1, date '2021-03-16', date '2021-03-18' from dual union all
select 'PROJ_002',	2, date '2021-03-17', date '2021-03-19' from dual union all
select 'PROJ_002',	3, date '2021-03-19', date '2021-03-19' from dual union all
select 'PROJ_002',	4, date '2021-03-21', date '2021-03-23' from dual ;
8 rows affected

select * from projects;


PROJECTNO
STAGES
STARTDATE
COMPLETIONDATE


PROJ_001
1
12-MAR-21
12-MAR-21

PROJ_001
2
14-MAR-21
14-MAR-21

PROJ_001
3
15-MAR-21
15-MAR-21

PROJ_001
4
18-MAR-21
18-MAR-21

PROJ_002
1
16-MAR-21
18-MAR-21

PROJ_002
2
17-MAR-21
19-MAR-21

PROJ_002
3
19-MAR-21
19-MAR-21

PROJ_002
4
21-MAR-21
23-MAR-21


select 
  projectno, stages, startdate, completiondate 
, case
    when startdate = completiondate then 'on time'
    else 'delayed'
  end output_1
from projects;


PROJECTNO
STAGES
STARTDATE
COMPLETIONDATE
OUTPUT_1


PROJ_001
1
12-MAR-21
12-MAR-21
on time

PROJ_001
2
14-MAR-21
14-MAR-21
on time

PROJ_001
3
15-MAR-21
15-MAR-21
on time

PROJ_001
4
18-MAR-21
18-MAR-21
on time

PROJ_002
1
16-MAR-21
18-MAR-21
delayed

PROJ_002
2
17-MAR-21
19-MAR-21
delayed

PROJ_002
3
19-MAR-21
19-MAR-21
on time

PROJ_002
4
21-MAR-21
23-MAR-21
delayed


select 
  projectno
, case 
    when max( startdate ) = max( completiondate ) then 'on time'
    else 'delayed'
  end output_2  
from projects
group by projectno
;


PROJECTNO
OUTPUT_2


PROJ_002
delayed

PROJ_001
on time


select Q1.*, Q2.output_2
from (
  select 
    projectno, stages, startdate, completiondate 
  , case
      when startdate = completiondate then 'on time'
      else 'delayed'
    end output_1
  from projects
) Q1 join (
  select 
    projectno
  , case 
      when max( startdate ) = max( completiondate ) then 'on time'
      else 'delayed'
    end output_2  
  from projects
  group by projectno
) Q2 on Q1.projectno = Q2.projectno 
order by Q1.projectno, Q1.startdate
;


PROJECTNO
STAGES
STARTDATE
COMPLETIONDATE
OUTPUT_1
OUTPUT_2


PROJ_001
1
12-MAR-21
12-MAR-21
on time
on time

PROJ_001
2
14-MAR-21
14-MAR-21
on time
on time

PROJ_001
3
15-MAR-21
15-MAR-21
on time
on time

PROJ_001
4
18-MAR-21
18-MAR-21
on time
on time

PROJ_002
1
16-MAR-21
18-MAR-21
delayed
delayed

PROJ_002
2
17-MAR-21
19-MAR-21
delayed
delayed

PROJ_002
3
19-MAR-21
19-MAR-21
on time
delayed

PROJ_002
4
21-MAR-21
23-MAR-21
delayed
delayed

PROJECTNO	STAGES	STARTDATE	COMPLETIONDATE
PROJ_001	1	12-MAR-21	12-MAR-21
PROJ_001	2	14-MAR-21	14-MAR-21
PROJ_001	3	15-MAR-21	15-MAR-21
PROJ_001	4	18-MAR-21	18-MAR-21
PROJ_002	1	16-MAR-21	18-MAR-21
PROJ_002	2	17-MAR-21	19-MAR-21
PROJ_002	3	19-MAR-21	19-MAR-21
PROJ_002	4	21-MAR-21	23-MAR-21