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
