Oracle - How to check values from multiple records and if one values matches - use it for larger dataset

Here's my data:

Step   random_task     Status         date
1        do_this      Completed       1-Nov-2020
1        do-that      Cancelled       2-Nov-2020
2        do_this     Not Assigned     1-Nov-2020
2        do_that      Cancelled       2-Nov-2020
2        and_that     Cancelled       2-Nov-2020
3        do_this      Cancelled       2-Nov-2020
3        do_that      Cancelled       2-Nov-2020
3        also_that    Cancelled       2-Nov-2020
4        do_that      In Progress or Scheduled or whatever   1-Nov-2020
4        and_that     Completed       2-Nov-2020

Grouping by Steps:

Completed = If all tasks in the Step have APS_Status__c = “Completed”.

Completed = If at least 1 Task is "Completed" and ALL remaining tasks = "Cancelled".

Not Started = If at least 1 Task is “Not Assigned” and ALL remaining tasks = "Cancelled".

Not Started = If all tasks in the Step have APS_Status__c = “Not Assigned”.

Cancelled = If all tasks in the Step have APS_Status__c = “Cancelled”.

In Progress = If at least 1 Task is <> "Completed" or "Cancelled" or "Not Assigned".

Output:

Step       Status            date 
1          Completed         2-Nov-2020 (if status = completed, then max(date) of that step
2          Not Started      (null)
3          Cancelled        (null)
4          In Progress      (null)

I have tried adding assigning values to like 100, -100 & 0, summing it up and then extracting mod(sum,100) to determing the correct value. But I am unable to assign the max(date) for that step.

Solution

You can use conditional aggregation as:

select step,
       (case when min(status) = max(status) and min(status) in ('Completed', 'Cancelled')
             then min(status)
             when sum(case when status in ('Completed', 'Cancelled') then 1 else 0 end) = count(*)
             then 'Completed'
             when sum(case when status in ('Not Assigned', 'Cancelled') then 1 else 0 end) = count(*)
             then 'Not Started'
             then ''
             else 'In Progress'
       end) as status,
       (case when min(status) = max(status) and min(status) in 'Completed'
             then max(date)
        end)
from t
group by step;

These slightly simplify your logic. In particular, the "Completed" and "Cancelled" are handled first in the logic -- if all statuses are the same. Then a step is "Not Started" if all statuses are "Not Assigned" or "Cancelled".