-- select * from BusinessFlows where name like '%FY21%' -- Select Approver_ID, Result, Count(*) from ( SELECT UserActions.ContextID ContextID, UserActions.userActionID user_action_id, UserActions.taskID task_id, BBExecutionContexts.businessFlowID campaign_id, BusinessFlows.name campaign_name, BBExecutionContexts.nodeName certifier_type, UserActions.owner approver_id, CASE BusinessFlowTasks.taskType WHEN 8 THEN 'Certify user role link' WHEN 9 THEN 'Certify user resource link' WHEN 10 THEN 'Certify role resource link' WHEN 11 THEN 'Certify role child role link' WHEN 12 THEN 'Certify role parent role link' WHEN 14 THEN 'Suggest adding new user role link' WHEN 15 THEN 'Suggest adding new user resource link' WHEN 16 THEN 'Suggest adding new role resource link' WHEN 17 THEN 'Suggest adding new role child role link' WHEN 18 THEN 'Suggest adding new role parent role link' ELSE 'Null' END task_type, CASE BBFE1.entityType WHEN 1 THEN 'User' WHEN 2 THEN 'Resource' WHEN 3 THEN 'Role' ELSE 'Unknown' END main_entity_type, BBFE1.displayName main_entity_name, BBFE1.Key1 as MainKey1, BBFE2.Key1 as SecRes1, BBFE2.Key2 as SecRes2, BBFE2.Key3 as SecRes3, CASE BBFE2.EntityType WHEN 1 THEN 'User' WHEN 2 THEN 'Resource' WHEN 3 THEN 'Role' ELSE 'Unknown' END secondary_entity_type, BBFE2.displayName secondary_entity_name, CASE TO_CHAR(nvl(BBExecutionContexts.result,'Pending')) WHEN 'result.certification.approve' THEN 'Approve' WHEN 'result.certification.reject' THEN 'Reject' WHEN 'result.approval.approve' THEN 'Approval Approve' WHEN 'result.approval.reject' THEN 'Approval Reject' WHEN 'result.suggest.approve' THEN 'Accept' WHEN 'result.suggest.reject' THEN 'Decline' WHEN 'Pending' THEN 'Pending' ELSE TO_CHAR(BBExecutionContexts.Result) END result, UserActions.result UserActionsResult, to_char(UserActions.resultStartDate, 'MM/DD/YY HH24:MI') decision_timestamp, BusinessFlowComments.commentText task_Comment FROM BBExecutionContexts INNER JOIN UserActions ON UserActions.contextID = BBExecutionContexts.contextID --AND UserActions.result not LIKE '%approve%' -- AND UserActions.resultModifier = UserActions.owner INNER JOIN BusinessFlows ON BusinessFlows.businessFlowID = BBExecutionContexts.businessFlowID INNER JOIN BusinessFlowEntities BBFE1 ON BBFE1.businessFlowID = BusinessFlows.businessFlowID AND BBFE1.entityID = UserActions.mainEntity INNER JOIN BusinessFlowEntities BBFE2 ON BBFE2.businessFlowID = BusinessFlows.businessFlowID AND BBFE2.entityID = UserActions.secondaryEntity INNER JOIN BusinessFlowTasks ON BusinessFlowTasks.businessFlowID = BusinessFlows.businessFlowID AND UserActions.taskID = BusinessFlowTasks.taskID INNER JOIN Configurations ON Configurations.configurationName = BusinessFlows.documentName LEFT OUTER JOIN BFTaskComments ON BusinessFlowTasks.businessFlowID = BFTaskComments.businessFlowID AND BusinessFlowTasks.taskID = BFTaskComments.taskID LEFT OUTER JOIN BusinessFlowComments ON BusinessFlowComments.commentID = BFTaskComments.commentID WHERE BusinessFlows.name like '%FY21%' --and BBExecutionContexts.result is null -- ie pending --AND BBExecutionContexts.businessFlowID = 591 -- and UserActions.resultStartDate > '12-APR-21' -- and UserActions.owner like '%8888' ORDER BY campaign_id, main_entity_name, secondary_entity_name, decision_timestamp /*) Group by Approver_ID, Result Order by Result, Count(*) Desc */