User1215529056 posted
I have production hours in a list of tables to show how much time is required to do a job on the cutter.
See query below:
SELECT
OrderValue.ProductionMinutes / 60 as hours
FROM OrderValue
INNER JOIN OrderProcess ON OrderValue.JobNumber = OrderProcess.JobNumber
AND OrderValue.ComponentNumber = OrderProcess.ComponentNumber
AND OrderValue.GroupNo = OrderProcess.GroupNo
AND OrderValue.ItemNumber = OrderProcess.ItemNumber
AND OrderValue.PartNumber = OrderProcess.PartNumber
INNER JOIN OrderQtyTable ON OrderValue.JobNumber = OrderQtyTable.JobNumber
AND OrderValue.ComponentNumber = OrderQtyTable.ComponentNumber
AND OrderValue.QuantityLineNo = OrderQtyTable.QuantityLineNo
LEFT JOIN Process ON OrderProcess.ProcessCode = Process.ProcessCode
INNER JOIN OrderComponent ON OrderValue.JobNumber = OrderComponent.JobNumber
AND OrderValue.ComponentNumber = OrderComponent.ComponentNumber
AND OrderValue.QuantityLineNo = OrderComponent.QtyOrdIndex
WHERE OrderProcess.JobNumber ='23385'
AND OrderProcess.PartNumber = 0
AND OrderProcess.DontPrintOnTicket = 0
AND Process.CostCenterCode = 5000
What I want to do is to limit the results based on what was done in production.
If the cutter has completed component 1 and still has to complete component 2 I want component 2 hours to be returned
I added this in the where clause but it does not like it and I can't seem to find out why and fix it.
It underlines in red Select
AND (Select
Production.CompletionCode
from Production
join ProductionJobNumber on Production.code=ProductionJobNumber.ProductionCode
join Process pr on Production.ProcessCode=Pr.ProcessCode
where OrderProcess.JobNumber =ProductionJobNumber.jobnumber
And Production.CompletionCode<>'30'
AND Pr.CostCenterCode=5000)