We are trying to figure out a way to calculate process time (user inputs) for a particular case where any activity has featured more than once i.e. Rework. Normally, we would calculate throughput time but in this case, we are gathering the relative information like process time for individual activity and then checking if there are any rework activities for a particular case.

Currently the query that is created is calculating the process time for all activities, however we want it to calculate only the activities which has happened more than once. Any kind of help is appreciated.

Query:

SUM

(CASE WHEN PROCESS NOT EQUALS 'Cancel Invoice' THEN 

     CASE WHEN PU_COUNT ( "BSEG", "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" ) <>

          PU_COUNT_DISTINCT ( "BSEG", "_CEL_AR_ACTIVITIES"."ACTIVITY_EN") THEN 

         CASE WHEN PU_COUNT ( DOMAIN_TABLE ( "_CEL_AR_ACTIVITIES"."_CASE_KEY"), "_CEL_AR_ACTIVITIES"."ACTIVITY_EN")) >1 

             THEN

           (CASE  

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Record Goods Issue' THEN <%= PT_Records_Goods_Invoice %>

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Create Invoice' THEN <%= PT_Create_Invoice %>

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Send Invoice' THEN <%= PT_Send_Invoice %>

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Invoice cleared' THEN <%= PT_Invoice_Cleared %>

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Due Date passed' THEN <%= PT_Due_Date_Passed %>

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Send Overdue Notice' THEN <%= PT_Send_Overdue_Notice %>

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Change Price' THEN <%= PT_Change_Price %> 

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Change Baseline Date' THEN <%= PT_Change_Baseline_Date %>

               WHEN "_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Change Payment Term' THEN <%= PT_Change_Payment_Term %>

               ELSE 0

           END)

         END

     END

END)