in my table I have a column for a project which can have multiple project milestones (second column). Each of the milestone can have the value 'fullfilled' or 'pending' as current_status (third column). Now I want to aggregate the overall status of each project and assign it a final status (new virtual column) for better overview.
The following rule should apply:
- If every milestone is fullfiled then the status of a project should change to 'Finished'
- If one or more milestones are pending then the status of project should be 'On-Hold'
Meaning that I want to checklist every current status per project to get a final_status.
Any idea how to do this with a case-when-clause or something similar?