SQL Server 2022

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

create table Project (ProjectId int, ProjectName varchar(64));
create table ProjectStatus (ProjectStatusId int, ProjectId int, ProjectStatus varchar(64), [Date] datetime2);
insert into Project (ProjectId, ProjectName)
values
(1,	'Project A'),
(2,	'Project B'),
(3,	'Project C'),
(4,	'Project D');
4 rows affected

insert into ProjectStatus(ProjectStatusId, ProjectId, ProjectStatus, [Date])
values
(1,	1,	'Started',	'2/10/2020'),
(2,	1,	'In Testing',	'5/6/2021'),
(3,	1,	'Finished',	'7/1/2021'),
(4,	2,	'In Testing',	'1/30/2019'),
(5,	2,	'Finished',	'3/18/2020'),
(6,	4,	'Started',	'10/22/2016'),
(7,	4,	'Finished',	'3/18/2020');
7 rows affected

-- Test for missing started when at least 1 status exists
select ProjectId, 'Started', '1/1/2000'
from ProjectStatus
group by ProjectId
having sum(case when ProjectStatus = 'Started' then 1 else 0 end) = 0;


ProjectId
(No column name)
(No column name)


2
Started
1/1/2000


insert into ProjectStatus (ProjectId, ProjectStatus, [Date])
select ProjectId, 'Started', '1/1/2000'
from Project p
where not exists (
  select 1
  from ProjectStatus ps
  where ps.ProjectId = p.ProjectId
  and ps.ProjectStatus = 'Started'
);
2 rows affected

select * from ProjectStatus;


ProjectStatusId
ProjectId
ProjectStatus
Date


1
1
Started
2020-02-10 00:00:00.0000000

2
1
In Testing
2021-05-06 00:00:00.0000000

3
1
Finished
2021-07-01 00:00:00.0000000

4
2
In Testing
2019-01-30 00:00:00.0000000

5
2
Finished
2020-03-18 00:00:00.0000000

6
4
Started
2016-10-22 00:00:00.0000000

7
4
Finished
2020-03-18 00:00:00.0000000

null
2
Started
2000-01-01 00:00:00.0000000

null
3
Started
2000-01-01 00:00:00.0000000

ProjectStatusId	ProjectId	ProjectStatus	Date
1	1	Started	2020-02-10 00:00:00.0000000
2	1	In Testing	2021-05-06 00:00:00.0000000
3	1	Finished	2021-07-01 00:00:00.0000000
4	2	In Testing	2019-01-30 00:00:00.0000000
5	2	Finished	2020-03-18 00:00:00.0000000
6	4	Started	2016-10-22 00:00:00.0000000
7	4	Finished	2020-03-18 00:00:00.0000000
null	2	Started	2000-01-01 00:00:00.0000000
null	3	Started	2000-01-01 00:00:00.0000000