First Pass Yield RATIO for Change Reqeusts

Question

I am trying to create a count function that counts the number of ECRs for which atleast one work item was given a 'Rework' vote, that way I can count how many ECRs were reworked atleast once and then divide that by total ECRs.

first pass yield = 1- (ECRs reworked atleast once/Total number of ECRs)

The problem I am facing is because the ECR is linked to work item as a PBO, the counts of ECRs is including each work item of a given ECR where the Rework vote was given.

Also, calculating this ratio seems almost impossible.

Do we need to just download the data and do the calculation in an external tool?

avillanueva · Answer

Careful what you wish for. I created this sql query to calculate turnbacks (part of QCPC in lean manufacturing). In this case, I was assigning turnbacks to days late of a process set with a defined duration. This also was from earlier Windchill version so schema might have changed. I had to handle what if the process was sent back around? How do we account for that? At this time, we dinged them for the entire loop back. Since then we do not so this was no longer needed. I had no hope of doing this in Query Builder. I ran in my SQL editor and downloaded to Excel. The result of this query was the number of turnbacks per "step or phase" in the review workflow.

SELECT TABEC.ECN, TABEC.RESOLUTIONDATE, TABHOLD.TURNBACK HOLD_TASK_TB, TABCCBR.TURNBACK CCB_REVIEW_TB,
 TABCCBCD.TURNBACK CCB_CHAIRDECISION_TB, TABAMEND.TURNBACK AMEND_TB, TABFINAL.TURNBACK FINAL_REWORK_TB, 
 TABAUDIT.TURNBACK CM_AUDIT_TB
FROM (SELECT DISTINCT M.WTCHGORDERNUMBER ECN, O.RESOLUTIONDATE
 FROM WTCHANGEORDER2 O, WTCHANGEORDER2MASTER M
 WHERE
 M.IDA2A2 = O.IDA3MASTERREFERENCE AND
 O.RESOLUTIONDATE >= TO_DATE('1/1/2019','MM/DD/YYYY') AND
 O.STATESTATE LIKE 'RELEASED') TABEC,
 (SELECT ECN, 
 (CASE WHEN TOTAL_TRIPS = 1
 THEN TURNBACK
 ELSE 
 (CASE WHEN MAX(FIRSTPASS)<3
 THEN ROUND((TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 ELSE ROUND(MAX(FIRSTPASS)-3+(TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 END)
 END) TURNBACK
 FROM
 (SELECT M.WTCHGORDERNUMBER ECN, 
 (CASE WHEN (AA.DEADLINE-AA.CREATESTAMPA2)<3 
 THEN (CASE WHEN ((AA.ENDTIME-AA.CREATESTAMPA2)-3)<0 THEN 0
 ELSE ROUND(((AA.ENDTIME-AA.CREATESTAMPA2)-3)+0.5)
 END)
 ELSE
 (CASE WHEN (AA.ENDTIME-AA.DEADLINE)<0 THEN 0 
 ELSE ROUND(AA.ENDTIME-AA.DEADLINE+0.5) 
 END)
 END) TURNBACK, AA.DEADLINE,AA.TRIPCOUNT TOTAL_TRIPS, (W1.MODIFYSTAMPA2-W1.CREATESTAMPA2) FIRSTPASS, 
 TEMP1.MODIFYSTAMPA2-TEMP1.CREATESTAMPA2 LOOPS
 FROM WTCHANGEORDER2 O, WFPROCESS WFP, WFASSIGNEDACTIVITY AA,WTCHANGEORDER2MASTER M, 
 (SELECT A2.IDA3A4,W2.MODIFYSTAMPA2, W2.CREATESTAMPA2
 FROM WFASSIGNMENT A2, WORKITEM W2
 WHERE
 W2.IDA3C4 = A2.IDA2A2 AND
 A2.TRIPCOUNT > 1 ) TEMP1, WFASSIGNMENT A1, WORKITEM W1
 WHERE
 M.IDA2A2 = O.IDA3MASTERREFERENCE AND
 WFP.IDA2A2 = AA.IDA3PARENTPROCESSREF AND
 A1.IDA3A4 = AA.IDA2A2 AND
 TEMP1.IDA3A4 (+) = AA.IDA2A2 AND
 AA.NAME LIKE 'Hold%' AND
 O.RESOLUTIONDATE >= TO_DATE('1/1/2019','MM/DD/YYYY') AND
 O.STATESTATE LIKE 'RELEASED' AND
 WFP.BUSINESSOBJREFERENCE LIKE 'VR:wt.change2.WTChangeOrder2%' AND
 O.BRANCHIDITERATIONINFO = TO_NUMBER(SUBSTR(WFP.BUSINESSOBJREFERENCE,30)) AND
 W1.IDA3C4 = A1.IDA2A2 AND
 A1.TRIPCOUNT = 1) TEMP
 GROUP BY ECN, TURNBACK, DEADLINE, TOTAL_TRIPS) TABHOLD,
 (SELECT ECN, TURNBACK, DEADLINE,TOTAL_TRIPS,FIRSTPASS, SUM(LOOPS) LOOPS
 FROM
 (SELECT ECN, TURNBACK, DEADLINE,TOTAL_TRIPS, MAX(FIRSTPASS) FIRSTPASS, AA_TRIP, MAX(LOOPS) LOOPS
 FROM
 (SELECT M.WTCHGORDERNUMBER ECN, 
 (CASE WHEN (AA.DEADLINE-AA.CREATESTAMPA2)<7 
 THEN (CASE WHEN ((AA.ENDTIME-AA.CREATESTAMPA2)-7)<0 THEN 0
 ELSE ROUND(((AA.ENDTIME-AA.CREATESTAMPA2)-7)+0.5)
 END)
 ELSE
 (CASE WHEN (AA.ENDTIME-AA.DEADLINE)<0 THEN 0 
 ELSE ROUND(AA.ENDTIME-AA.DEADLINE+0.5) 
 END)
 END) TURNBACK, AA.DEADLINE,AA.TRIPCOUNT TOTAL_TRIPS, (W1.MODIFYSTAMPA2-W1.CREATESTAMPA2) FIRSTPASS,
 AA.TRIPCOUNT AA_TRIP, TEMP1.MODIFYSTAMPA2-TEMP1.CREATESTAMPA2 LOOPS
 FROM WTCHANGEORDER2 O, WFPROCESS WFP, WFASSIGNEDACTIVITY AA,WTCHANGEORDER2MASTER M, 
 (SELECT A2.IDA3A4,W2.MODIFYSTAMPA2, W2.CREATESTAMPA2
 FROM WFASSIGNMENT A2, WORKITEM W2
 WHERE
 W2.IDA3C4 = A2.IDA2A2 AND
 A2.TRIPCOUNT > 1 ) TEMP1, WFASSIGNMENT A1, WORKITEM W1
 WHERE
 M.IDA2A2 = O.IDA3MASTERREFERENCE AND
 WFP.IDA2A2 = AA.IDA3PARENTPROCESSREF AND
 A1.IDA3A4 = AA.IDA2A2 AND
 TEMP1.IDA3A4 (+) = AA.IDA2A2 AND
 AA.NAME LIKE 'CCB Review' AND
 O.RESOLUTIONDATE >= TO_DATE('1/1/2019','MM/DD/YYYY') AND
 O.STATESTATE LIKE 'RELEASED' AND
 WFP.BUSINESSOBJREFERENCE LIKE 'VR:wt.change2.WTChangeOrder2%' AND
 O.BRANCHIDITERATIONINFO = TO_NUMBER(SUBSTR(WFP.BUSINESSOBJREFERENCE,30)) AND
 W1.IDA3C4 = A1.IDA2A2 AND
 A1.TRIPCOUNT = 1)
 GROUP BY ECN, TURNBACK, DEADLINE,TOTAL_TRIPS, AA_TRIP)
 GROUP BY ECN, TURNBACK, DEADLINE,TOTAL_TRIPS, FIRSTPASS) TABCCBR,
 (SELECT ECN, 
 (CASE WHEN TOTAL_TRIPS = 1
 THEN TURNBACK
 ELSE 
 (CASE WHEN MAX(FIRSTPASS)<1
 THEN ROUND((TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 ELSE ROUND(MAX(FIRSTPASS)-1+(TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 END)
 END) TURNBACK
 FROM
 (SELECT M.WTCHGORDERNUMBER ECN, 
 (CASE WHEN (AA.DEADLINE-AA.CREATESTAMPA2)<1 
 THEN (CASE WHEN ((AA.ENDTIME-AA.CREATESTAMPA2)-1)<0 THEN 0
 ELSE ROUND(((AA.ENDTIME-AA.CREATESTAMPA2)-1)+0.5)
 END)
 ELSE
 (CASE WHEN (AA.ENDTIME-AA.DEADLINE)<0 THEN 0 
 ELSE ROUND(AA.ENDTIME-AA.DEADLINE+0.5) 
 END)
 END) TURNBACK, AA.DEADLINE,AA.TRIPCOUNT TOTAL_TRIPS, (W1.MODIFYSTAMPA2-W1.CREATESTAMPA2) FIRSTPASS, 
 TEMP1.MODIFYSTAMPA2-TEMP1.CREATESTAMPA2 LOOPS
 FROM WTCHANGEORDER2 O, WFPROCESS WFP, WFASSIGNEDACTIVITY AA,WTCHANGEORDER2MASTER M, 
 (SELECT A2.IDA3A4,W2.MODIFYSTAMPA2, W2.CREATESTAMPA2
 FROM WFASSIGNMENT A2, WORKITEM W2
 WHERE
 W2.IDA3C4 = A2.IDA2A2 AND
 A2.TRIPCOUNT > 1 ) TEMP1, WFASSIGNMENT A1, WORKITEM W1
 WHERE
 M.IDA2A2 = O.IDA3MASTERREFERENCE AND
 WFP.IDA2A2 = AA.IDA3PARENTPROCESSREF AND
 A1.IDA3A4 = AA.IDA2A2 AND
 TEMP1.IDA3A4 (+) = AA.IDA2A2 AND
 AA.NAME LIKE 'CCB Chairperson Decision' AND
 O.RESOLUTIONDATE >= TO_DATE('1/1/2019','MM/DD/YYYY') AND
 O.STATESTATE LIKE 'RELEASED' AND
 WFP.BUSINESSOBJREFERENCE LIKE 'VR:wt.change2.WTChangeOrder2%' AND
 O.BRANCHIDITERATIONINFO = TO_NUMBER(SUBSTR(WFP.BUSINESSOBJREFERENCE,30)) AND
 W1.IDA3C4 = A1.IDA2A2 AND
 A1.TRIPCOUNT = 1) TEMP
 GROUP BY ECN, TURNBACK, DEADLINE, TOTAL_TRIPS) TABCCBCD,
 (SELECT ECN, 
 (CASE WHEN TOTAL_TRIPS = 1
 THEN ROUND(TURNBACK+0.5)
 ELSE 
 ROUND(MAX(FIRSTPASS)-1+(TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 END) TURNBACK
 FROM
 (SELECT M.WTCHGORDERNUMBER ECN,(AA.ENDTIME-AA.CREATESTAMPA2) TURNBACK, AA.DEADLINE,AA.TRIPCOUNT TOTAL_TRIPS, 
 (W1.MODIFYSTAMPA2-W1.CREATESTAMPA2) FIRSTPASS, TEMP1.MODIFYSTAMPA2-TEMP1.CREATESTAMPA2 LOOPS
 FROM WTCHANGEORDER2 O, WFPROCESS WFP, WFASSIGNEDACTIVITY AA,WTCHANGEORDER2MASTER M, 
 (SELECT A2.IDA3A4,W2.MODIFYSTAMPA2, W2.CREATESTAMPA2
 FROM WFASSIGNMENT A2, WORKITEM W2
 WHERE
 W2.IDA3C4 = A2.IDA2A2 AND
 A2.TRIPCOUNT > 1 ) TEMP1, WFASSIGNMENT A1, WORKITEM W1
 WHERE
 M.IDA2A2 = O.IDA3MASTERREFERENCE AND
 WFP.IDA2A2 = AA.IDA3PARENTPROCESSREF AND
 A1.IDA3A4 = AA.IDA2A2 AND
 TEMP1.IDA3A4 (+) = AA.IDA2A2 AND
 AA.NAME LIKE 'Amend%' AND
 O.RESOLUTIONDATE >= TO_DATE('1/1/2019','MM/DD/YYYY') AND
 O.STATESTATE LIKE 'RELEASED' AND
 WFP.BUSINESSOBJREFERENCE LIKE 'VR:wt.change2.WTChangeOrder2%' AND
 O.BRANCHIDITERATIONINFO = TO_NUMBER(SUBSTR(WFP.BUSINESSOBJREFERENCE,30)) AND
 W1.IDA3C4 = A1.IDA2A2 AND
 A1.TRIPCOUNT = 1) TEMP
 GROUP BY ECN, TURNBACK, DEADLINE, TOTAL_TRIPS) TABAMEND,
 (SELECT ECN, 
 (CASE WHEN TOTAL_TRIPS = 1
 THEN TURNBACK
 ELSE 
 (CASE WHEN MAX(FIRSTPASS)<1
 THEN ROUND((TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 ELSE ROUND(MAX(FIRSTPASS)-1+(TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 END)
 END) TURNBACK
 FROM
 (SELECT M.WTCHGORDERNUMBER ECN, 
 (CASE WHEN ((AA.DEADLINE IS NULL) OR (AA.DEADLINE-AA.CREATESTAMPA2)<1) 
 THEN (CASE WHEN ((AA.ENDTIME-AA.CREATESTAMPA2)-1)<0 THEN 0
 ELSE ROUND(((AA.ENDTIME-AA.CREATESTAMPA2)-1)+0.5)
 END)
 ELSE
 (CASE WHEN (AA.ENDTIME-AA.DEADLINE)<0 THEN 0 
 ELSE ROUND(AA.ENDTIME-AA.DEADLINE+0.5) 
 END)
 END) TURNBACK, AA.DEADLINE,AA.TRIPCOUNT TOTAL_TRIPS, (W1.MODIFYSTAMPA2-W1.CREATESTAMPA2) FIRSTPASS, 
 TEMP1.MODIFYSTAMPA2-TEMP1.CREATESTAMPA2 LOOPS
 FROM WTCHANGEORDER2 O, WFPROCESS WFP, WFASSIGNEDACTIVITY AA,WTCHANGEORDER2MASTER M, 
 (SELECT A2.IDA3A4,W2.MODIFYSTAMPA2, W2.CREATESTAMPA2
 FROM WFASSIGNMENT A2, WORKITEM W2
 WHERE
 W2.IDA3C4 = A2.IDA2A2 AND
 A2.TRIPCOUNT > 1 ) TEMP1, WFASSIGNMENT A1, WORKITEM W1
 WHERE
 M.IDA2A2 = O.IDA3MASTERREFERENCE AND
 WFP.IDA2A2 = AA.IDA3PARENTPROCESSREF AND
 A1.IDA3A4 = AA.IDA2A2 AND
 TEMP1.IDA3A4 (+) = AA.IDA2A2 AND
 (AA.NAME LIKE 'Finalize%' OR AA.NAME LIKE 'Rework%') AND
 O.RESOLUTIONDATE >= TO_DATE('1/1/2019','MM/DD/YYYY') AND
 O.STATESTATE LIKE 'RELEASED' AND
 WFP.BUSINESSOBJREFERENCE LIKE 'VR:wt.change2.WTChangeOrder2%' AND
 O.BRANCHIDITERATIONINFO = TO_NUMBER(SUBSTR(WFP.BUSINESSOBJREFERENCE,30)) AND
 W1.IDA3C4 = A1.IDA2A2 AND
 A1.TRIPCOUNT = 1) TEMP
 GROUP BY ECN, TURNBACK, DEADLINE, TOTAL_TRIPS) TABFINAL,
 (SELECT ECN, 
 (CASE WHEN TOTAL_TRIPS = 1
 THEN TURNBACK
 ELSE 
 (CASE WHEN MAX(FIRSTPASS)<3
 THEN ROUND((TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 ELSE ROUND(MAX(FIRSTPASS)-3+(TOTAL_TRIPS-1)+ SUM(LOOPS)+0.5)
 END)
 END) TURNBACK
 FROM
 (SELECT M.WTCHGORDERNUMBER ECN, 
 (CASE WHEN (AA.DEADLINE-AA.CREATESTAMPA2)<3 
 THEN (CASE WHEN ((AA.ENDTIME-AA.CREATESTAMPA2)-3)<0 THEN 0
 ELSE ROUND(((AA.ENDTIME-AA.CREATESTAMPA2)-3)+0.5)
 END)
 ELSE
 (CASE WHEN (AA.ENDTIME-AA.DEADLINE)<0 THEN 0 
 ELSE ROUND(AA.ENDTIME-AA.DEADLINE+0.5) 
 END)
 END) TURNBACK, AA.DEADLINE,AA.TRIPCOUNT TOTAL_TRIPS, (W1.MODIFYSTAMPA2-W1.CREATESTAMPA2) FIRSTPASS, 
 TEMP1.MODIFYSTAMPA2-TEMP1.CREATESTAMPA2 LOOPS
 FROM WTCHANGEORDER2 O, WFPROCESS WFP, WFASSIGNEDACTIVITY AA,WTCHANGEORDER2MASTER M, 
 (SELECT A2.IDA3A4,W2.MODIFYSTAMPA2, W2.CREATESTAMPA2
 FROM WFASSIGNMENT A2, WORKITEM W2
 WHERE
 W2.IDA3C4 = A2.IDA2A2 AND
 A2.TRIPCOUNT > 1 ) TEMP1, WFASSIGNMENT A1, WORKITEM W1
 WHERE
 M.IDA2A2 = O.IDA3MASTERREFERENCE AND
 WFP.IDA2A2 = AA.IDA3PARENTPROCESSREF AND
 A1.IDA3A4 = AA.IDA2A2 AND
 TEMP1.IDA3A4 (+) = AA.IDA2A2 AND
 AA.NAME LIKE 'Audit EC' AND
 O.RESOLUTIONDATE >= TO_DATE('1/1/2019','MM/DD/YYYY') AND
 O.STATESTATE LIKE 'RELEASED' AND
 WFP.BUSINESSOBJREFERENCE LIKE 'VR:wt.change2.WTChangeOrder2%' AND
 O.BRANCHIDITERATIONINFO = TO_NUMBER(SUBSTR(WFP.BUSINESSOBJREFERENCE,30)) AND
 W1.IDA3C4 = A1.IDA2A2 AND
 A1.TRIPCOUNT = 1) TEMP
 GROUP BY ECN, TURNBACK, DEADLINE, TOTAL_TRIPS) TABAUDIT
WHERE
TABEC.ECN=TABHOLD.ECN (+) AND
TABEC.ECN=TABCCBR.ECN AND
TABEC.ECN=TABCCBCD.ECN AND
TABEC.ECN=TABAMEND.ECN (+) AND
TABEC.ECN=TABFINAL.ECN (+) AND
TABEC.ECN=TABAUDIT.ECN

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded