SQL Query - Indeterminate Process Log

 Your process log has several workflows broken step by numbers with the possible status values of Complete , Running or Error.

Your task is write SQL statement that creates overall status based upon the following requirements.

1. If all the workflow steps have a status of complete , set overall status as complete. (ex. Bravo)

2. If all the workflow steps have a status of Error , set overall status as Error. (ex. Foxtrot)

3. If all the workflow steps have the combination of error and complete , set overall status should be Indeterminate. (ex. Alpha , Charlie and Echo)

4. If all the workflow steps have the combination of Running and complete , set overall status should be Running. (ex. Delta)

Source:

Expected Output:

Source:

Output:

Script:

WITH PROCESS_LOG AS (

SELECT 'Alpha' Workflow,1 stepno, 'Error' Status FROM DUAL

UNION ALL

SELECT 'Alpha' Workflow,2 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Bravo' Workflow,1 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Bravo' Workflow,2 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Charlie' Workflow,1 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Charlie' Workflow,2 stepno, 'Error' Status FROM DUAL

UNION ALL

SELECT 'Delta' Workflow,1 stepno, 'Complete' Status FROM DUAL

UNION ALL

SELECT 'Delta' Workflow,2 stepno, 'Running' Status FROM DUAL

UNION ALL

SELECT 'Echo' Workflow,1 stepno, 'Running' Status FROM DUAL

UNION ALL

SELECT 'Echo' Workflow,2 stepno, 'Error' Status FROM DUAL

UNION ALL

SELECT 'Foxtrot' Workflow,1 stepno, 'Error' Status FROM DUAL)

--decode(status,'Error','Indetermine',status) 

select workflow,status from (

select workflow,

case when status='Error' 

and count(rnk) over (partition by workflow) > 1

then 'Indetermine' else status end  Status,

rnk

from (

SELECT WORKFLOW, STEPNO, STATUS,

DENSE_RANK() OVER (PARTITION BY WORKFLOW ORDER BY 

                        CASE WHEN STATUS='Error' then 1 

                        When Status = 'Running' then 2

                        when status='Complete' then 3 end,stepno) rnk

FROM PROCESS_LOG))

where rnk = 1