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 |