Hi all,
I have a bit of an issue with an application that i am currently developing.
The application logs passed and failure information for a production line.
Each production lineis broken down into operations, each operation has a task group and then each task group has a bunch of tasks.
The failure rate of each task is logged so the following would happen:
Fit solenoid Fit Cover Fit Label
1 3 6
Also for each operation the pass rate is logged.
So the above tasks would come under Operation1.
Operation1 pass rate is 200
So to sum up Operation1 has 200 passed operations and a toal of 10 failures.
I am currently storing the Task failures in one table and the Operation passes in another table.
What i need to do is pull out all of the Task failures for each operation and sum up the values, this i can output without any issues.
This is where i get stuck: i also need to pull out the Operation passes per operation and sum them up in the same query.
My code returns incorrect sum values for the Pass values.
The data is loaded on a daily basis so Operation1 may have 200 passes today and then 15 the next.
Here is the code that i have right now:
<cfquery datasource="UKCHAPP145" name="Q2">
SELECT
SUM(TBL_PASSED.PASSED_QTY) AS TotalPassedQty,
SUM(TBL_FTBR_DATA.FTBR_FAILED_QTY) AS TotalFailedQty,
TBL_FTBR_DATA.FTBR_OPERATION_UUID,
TBL_PASSED.OPERATION_UUID,
TBL_OPERATION.OPERATION_NAME,
TBL_OPERATION.OPERATION_SORT_ORDER
FROM
UKCHAPP145.TBL_FTBR_DATA,
UKCHAPP145.TBL_OPERATION,
UKCHAPP145.TBL_PASSED
WHERE
TBL_FTBR_DATA.FTBR_OPERATION_UUID = TBL_OPERATION.OPERATION_UUID
AND
TBL_FTBR_DATA.FTBR_OPERATION_UUID = TBL_PASSED.OPERATION_UUID
GROUP BY
TBL_FTBR_DATA.FTBR_OPERATION_UUID,
TBL_OPERATION.OPERATION_NAME,
TBL_OPERATION.OPERATION_SORT_ORDER,
TBL_PASSED.OPERATION_UUID
ORDER BY
TBL_OPERATION.OPERATION_SORT_ORDER
ASC
</cfquery>
The above code outputs the following:
Op Name: FV OP1 - Failed Qty:0 Passed Qty: 5550
Op Name: FV OP 2 - Failed Qty:40 Passed Qty: 415
Op Name: COVER - Failed Qty:0 Passed Qty; 175
The passed Qty's are incorrect.
They should be :
Op Name: FV OP1 - Failed Qty:0 Passed Qty: 370
Op Name: FV OP 2 - Failed Qty:40 Passed Qty: 83
Op Name: COVER - Failed Qty:0 Passed Qty: 25
I am struggling to get the sql correct, can anyone help me out?
Many thanks
JM
Marked Solved 
