My question is more related to the database level. I am working on production module; more specifically, PRODTABLE (contains production order header), PRODJOURNALPROD (contains reports as finished production journal lines) and PRODJOURNALPROD (contains all transactions created when posting posting a line from route sheet). I am struggling to find a relationship between these tables, as I need to formulate a query using these three tables. I tried to inner join using PRODID, but that leads to a cartesian join between JOURNAL and ROUTE table. Any suggestions?
Following querry which I am trying for my Monthly Production Report.
SELECT
PT.PRODID,
PT.ITEMID,
PT.[NAME] AS ITEMNAME,
SUM(PT.QTYSCHED) AS QTYSCHED,
CONVERT(VARCHAR(12),PJ.TransDate,103) AS FINISHDATE,
SUM(PJ.QTYGOOD) AS GOODQTY,
SUM(PJ.QTYERROR) AS REJECTEDQTY,
WT.PRODUNITID,
SUM(PRT.HOURS) AS HOURS,
SUM(PR.CalcSetUp+PR.CalcProc) AS STDTIME,
CASE WHEN PJ.ERRORCAUSE=‘0’ THEN
‘NONE’
ELSE
CASE WHEN PJ.ERRORCAUSE=‘1’ THEN
‘Material’
ELSE
CASE WHEN PJ.ERRORCAUSE=‘2’ THEN
‘Machine’
ELSE
CASE WHEN PJ.ERRORCAUSE=‘3’ THEN
‘OperatingStaff’
END
END
END
END AS ERRORCAUSE
FROM PRODTABLE AS PT
INNER JOIN PRODJOURNALPROD AS PJ ON PT.PRODID=PJ.PRODID AND PT.DATAAREAID=PJ.DATAAREAID
INNER JOIN ProdRouteTrans AS PRT ON PT.PRODID=PRT.PRODID AND PT.DATAAREAID=PRT.DATAAREAID
INNER JOIN WrkCtrTable AS WT ON PRT.WRKCTRID=WT.WRKCTRID AND PRT.DATAAREAID=WT.DATAAREAID
INNER JOIN PRODROUTE AS PR ON PT.PRODID=PR.PRODID AND PT.DATAAREAID=PR.DATAAREAID
AND PR.OPRID=PRT.OPRID
WHERE PRT.DateWIP=@DATE AND PT.DATAAREAID=@COMPANY AND CONVERT(VARCHAR(12),PJ.TransDate,101)=@DATE and WT.PRODUNITID=@DEPARTMENT AND PRT.TRANSTYPE=‘0’ AND PRT.CategoryId LIKE’%Proc’
–WHERE PT.DATAAREAID=‘EPL’ AND CONVERT(VARCHAR(12),PJ.TransDate,103)=‘30/06/2011’
–AND PRT.CategoryId LIKE’%Proc’ OR PRT.CategoryId=’%Setup’ AND PRT.TRANSTYPE=‘0’
AND PRT.CategoryId LIKE’%Proc’AND PRT.TRANSTYPE=‘0’
GROUP BY
PT.PRODID,
PT.ITEMID,
PT.[NAME],
PJ.TransDate,
WT.PRODUNITID,
PJ.ERRORCAUSE