Hi,
We have Five table: tblPlant tblLine, tblproduct, tblProcessStep , tblMoveHistory
The relation between the table are
tblPlant -tblLine : 1 -*
tblLine - tblproduct *-*
tblline - tblProcessStep 1 - *
tblMoveHistory contains the productID, stepID and lineID, moveTime and the result (which is pass or failed).
now we have to calculate the totalNoOfPass/totalNoOfprocessed (pass+failed) as a percentage % and group by /filter by: date from-date to, bucket (intervals), plant, line, product, processStep. We did a report that calculate per intervals in part and per all these grouping the number of pass , and another report that calculate the total number process again at each group plantID, lineID, productID, stepID, dataInterval
now the problem was to link these two reports together or to modify one: so I can have per each of this group the percentage total Pass/ total process.
Here is what we need achieved using sql
SELECT processed.plantName,
processed.plantID,
processed.lineName,
processed.lineID,
processed.Product,
processed.productID,
processed.stepName,
processed.stepID,
processed. dateInterval,
passed. passNO/processed .processNO AS FINAL RESULT
FROM
(
SELECT p.PlantID,
p.PlantName,
l.LineID,
l.LineName,
pd.Product,
pd.ProductID,
ps.StepName,
ps.StepID,
count(mh.result) AS processNO,
@intv:= TRUNCATE((DAY(mh.MoveTime) – DAY(@fromD))/@bucket,0) AS dateInterval
FROM tblMoveHistory AS mh INNER JOIN tblProducts.ProductID = mh.ProductID
INNER JOIN tblprocesssteps ps ON ps.StepID = mh.OldStep
INNER JOIN tblLine l ON l.LineID = mh.lineID
INNER JOIN tblPlant p ON l.PlantID = p.PlantID
WHERE mh.moveTime BETWEEN @fromD AND @endD
p.plantIDlike '%%' AND
ps.stepID like '%%' AND
l.lineID like '%line%' AND
pd.productID like '%%'
GROUP BY p.plantID, l.lineID, pd.productID, ps.stepID, DateInterval
)
AS processed
INNER JOIN (
SELECT p.PlantID,
p.PlantName,
l.LineID,
l.LineName,
pd.Product,
pd.ProductID,
ps.StepName,
ps.StepID,
count(mh.result) AS passNO
@intv:= TRUNCATE((DAY(mh.MoveTime) – DAY(@fromD))/@bucket,0) AS dateInterval
FROM tblMoveHistory AS mh INNER JOIN tblProducts.ProductID = mh.ProductID
INNER JOIN tblprocesssteps ps ON ps.StepID = mh.OldStep
INNER JOIN tblLine l ON l.LineID = mh.lineID
INNER JOIN tblPlant p ON l.PlantID = p.PlantID
WHERE mh.moveTime BETWEEN @fromD AND @endD
p.plantIDlike '%%' AND
ps.stepID like '%%' AND
l.lineID like '%line%' AND
pd.productID like '%%'
mh.result like 'pass'
GROUP BY p.plantID, l.lineID, pd.productID, ps.stepID, dateInterval
)
AS passed
)
ON (passed .plantID = processed.plantID AND passed .lineID = processed.lineID AND
passed .productID = processed.productID AND passed .stepID = processed.stepID, passed .dateInterval= processed.dateInterval)
/*this line is not essencial cause they are already grouped*/
GROUP BY processed.plantID, processed.lineID, processed.productID, processed.stepID, processed.dateInterval
Thanks
