I’m trying to write a query that shows the difference between the picking activated and packing delivery dates, as a warehouse performance report. Has anyone had any luck linking these tables together. Every attempt I make will return the wrong packing slip or other bad information.
This was my last attempt…
SELECT WMSPICKINGROUTE.PICKINGROUTEID, SALESTABLE.SALESID, CUSTPACKINGSLIPJOUR.PACKINGSLIPID, SALESLINE.ITEMID, SALESTABLE.SALESNAME,
SALESTABLE.DATAAREAID, WMSPICKINGROUTE.INVENTLOCATIONID, CUSTPACKINGSLIPJOUR.DLVMODE, CUSTPACKINGSLIPJOUR.DLVMODTYPE,
SALESLINE.CREATEDDATETIME, WMSPICKINGROUTE.ACTIVATIONDATETIME, CUSTPACKINGSLIPJOUR.DELIVERYDATE
FROM WMSPICKINGROUTE INNER JOIN
SALESLINE ON WMSPICKINGROUTE.DATAAREAID = SALESLINE.DATAAREAID AND WMSPICKINGROUTE.TRANSREFID = SALESLINE.SALESID AND
WMSPICKINGROUTE.CUSTOMER = SALESLINE.CUSTACCOUNT INNER JOIN
SALESTABLE ON SALESLINE.SALESID = SALESTABLE.SALESID AND WMSPICKINGROUTE.CUSTOMER = SALESTABLE.CUSTACCOUNT AND
WMSPICKINGROUTE.INVENTLOCATIONID = SALESTABLE.INVENTLOCATIONID AND WMSPICKINGROUTE.TRANSREFID = SALESTABLE.SALESID INNER JOIN
CUSTPACKINGSLIPJOUR ON WMSPICKINGROUTE.DATAAREAID = CUSTPACKINGSLIPJOUR.DATAAREAID AND
SALESLINE.SALESID = CUSTPACKINGSLIPJOUR.SALESID AND SALESTABLE.SALESID = CUSTPACKINGSLIPJOUR.SALESID AND
SALESLINE.QTYORDERED = CUSTPACKINGSLIPJOUR.QTY AND WMSPICKINGROUTE.ACTIVATIONDATETIME < CUSTPACKINGSLIPJOUR.DELIVERYDATE AND
SALESLINE.DATAAREAID = CUSTPACKINGSLIPJOUR.DATAAREAID AND SALESTABLE.DATAAREAID = CUSTPACKINGSLIPJOUR.DATAAREAID INNER JOIN
CUSTPACKINGSLIPTRANS ON WMSPICKINGROUTE.DATAAREAID = CUSTPACKINGSLIPTRANS.DATAAREAID AND
CUSTPACKINGSLIPJOUR.SALESID = CUSTPACKINGSLIPTRANS.SALESGROUP