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?
Thank you.
Kind regards.
Advertisement
Answer
If there are only two statuses, then check whether the only status is ‘finished’.
Sample data in lines #1 – 9:
- project 1 is finished as its all milestone statuses are “fulfilled”
- project 2 is still on hold as only milestone is “fulfilled”
SQL> with project (projno, mileno, milestatus) as 2 (select 1, 1, 'fulfilled' from dual union all 3 select 1, 2, 'fulfilled' from dual union all 4 select 1, 3, 'fulfilled' from dual union all 5 -- 6 select 2, 1, 'fulfilled' from dual union all 7 select 2, 2, 'pending' from dual union all 8 select 2, 3, 'pending' from dual 9 ) 10 select projno, 11 case when min_status = max_status and 12 max_status = 'fulfilled' 13 then 'Finished' 14 else 'On hold' 15 end project_status 16 from (select projno, 17 min(milestatus) min_status, 18 max(milestatus) max_status 19 from project 20 group by projno 21 ); PROJNO PROJECT_---------- -------- 1 Finished 2 On holdSQL>