Member Avatar for jedimatt

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

  • 1 Contributor
  • 1 Reply
  • 157 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by jedimatt
Member Avatar for jedimatt

I managed to get this to work by creating two views.
One for the failed qty table and one for the passed qty table.
Each view summed the failed and passed qtys and i then joined both tables using the op id fields, works just fine.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.