Site status classficiation based on different project status

Dear community:

I have two tables one for site status, and the other table with project status by site where one site may have multiple projects.

In the site status table I have a column for status, but I need this colums to be calculated based on a logic wich considers the different status for projects assigned to that site in the project status table. I have a relationship built for both tables using the site column.

This is the logic:

If at least one project is running, the site status is running (no matter is there are other projects with status closed or not started in the project status table)

If all projects are closed, then the sites status is closed

If all projects are not yet started then the site status is planned

If there are no projects for a site, then the site status is not planned

Here is an example:

Project Status Table

Then, using the logic, I shoud see the following values in the site status colum:

As site D is not listed in the project status table, the status for that site should be Not Planned

Thanks in advance for your support!