question

Here is my sample data:

DECLARE @Project TABLE(Id INT, [Name] Varchar(50), ProjectPhase varchar(10), Created datetime);
INSERT INTO @Project Values
(1, 'Project1','I', GETDATE()),
(2, 'Project2','II', GETDATE()),
(3, 'Project3','IV', GETDATE()),
(4, 'Project4','V', GETDATE()),
(5, 'Project5','III', GETDATE()),
(6, 'Project6','II', GETDATE())

--- ProjectId (ForeingKey - Project)
DECLARE @Files TABLE(Id INT, ProjectId INT, Title Varchar(50), Ver Varchar(5), [Status] varchar(20), Created datetime);
INSERT INTO @Files Values
(1, 1, 'Title1','1.0', 'Initiated', GETDATE()),
(2, 1, 'Title1','2.0', 'Rejected', GETDATE()),
(3, 1, 'Title1','3.0', 'Completed', GETDATE()),
(4, 2, 'Title1','1.0', 'InProgress', GETDATE()),
(5, 2, 'Title1','2.0', 'Initiated', GETDATE()),
(6, 2, 'Title1','3.0', 'Approved', GETDATE()),
(7, 2, 'Title1','4.0', 'Completed', GETDATE()),
(8, 2, 'Title1','5.0', 'Completed', GETDATE()),
(9, 3, 'Title1','1.0', 'Completed', GETDATE()),
(10, 3, 'Title1','2.0', 'Completed', GETDATE()),
(11, 3, 'Title1','3.0', 'Completed', GETDATE()),
(12, 4, 'Title1','1.0', 'Approved', GETDATE()),
(13, 4, 'Title1','2.0', 'Approved', GETDATE()),
(14, 5, 'Title1','1.0', 'InProgress', GETDATE()),
(15, 6, 'Title1','1.0', 'Completed', GETDATE()),
(16, 6, 'Title1','2.0', 'Approved', GETDATE()),
(17, 6, 'Title1','3.0', 'Approved', GETDATE()),
(18, 6, 'Title1','4.0', 'Completed', GETDATE())


--Select P.Id, P.[Name], P.ProjectPhase, P.Created From @Project p Inner Join @Files f on 
--p.Id = F.ProjectId

1) Create a new runtime column 'ProjectStatus' in final output considering calculation logic

**#Calculation Logic for ProjectStatus column#

1) If ANY record in @Files table has 'Initiated' Status then ProjectStatus is 'Project Initiated'

2) IF ALL records in @Files table have 'Approved' Status then ProjectStatus is 'Fully Approved'

3) IF ALL records in @Files table have 'Completed' Status then ProjectStatus is 'Live'

4) IF All records in @Files table have 'Approved' AND 'Completed' Status then ProjectStatus is 'Live'

5) IF Any OR All records have status like InProgress, Submitted, OnHold Status then ProjectStatus is 'InProgress'

DECLARE @ExpectedResult TABLE(Id INT, [Name] Varchar(50),  ProjectPhase varchar(10), ProjectStatus varchar(30), Created datetime);
INSERT INTO @ExpectedResult Values
(1, 'Project1','I', 'Project Initiated',  GETDATE()),
(2, 'Project2','II', 'Project Initiated', GETDATE()),
(3, 'Project3','IV', 'Live', GETDATE()),
(4, 'Project4','V', 'Fully Approved', GETDATE()),
(5, 'Project5','III', 'InProgress', GETDATE()),
(6, 'Project6','II', 'Live', GETDATE())

Select * From @ExpectedResult

Thank You