Hi, I am trying to get the correct query to check Wh transfers where Qty Transferred < Qty Received in IN_SOH. Seems fine for InTransit records if I link WH_TransferLineDetails.InTransit_In_SohID but can't work out the other links in WH_TransferLineDetails to IN_SOH.
Linking WH_TransferLines.WH_TransferLineID = IN_SOH.SourceID seems to work in some instances. Can you explain how the links from the WH_Transfer files to IN_SOH should be linked. Help still details V6. Thanks.
See attachment plus Select query below :
SELECT WH_Transfer.TransferNo, WH_Transfer.TransferDate, WH_TransferLines.LineNum, WH_TransferLines.PartNo, WH_TransferLines.QtyWanted, WH_TransferLines.QtyTrans,
WH_TransferLineDetails.Quantity AS DetailQty, IN_SOH.QuantityIn, WH_TransferLineDetails.Cost, (IN_SOH.QuantityIn - WH_TransferLines.QtyTrans) * WH_TransferLineDetails.Cost AS Difference,
IN_SOH.QuantityLeft, IN_SOH.DateIn, IN_SOH.ExpiryDate, WH_Transfer.Type
FROM WH_Transfer INNER JOIN
WH_TransferLines ON WH_Transfer.WH_TransferID = WH_TransferLines.WH_TransferID INNER JOIN
WH_TransferLineDetails ON WH_TransferLines.WH_TransferLineID = WH_TransferLineDetails.WH_TransferLineID INNER JOIN
IN_SOH ON WH_TransferLines.WH_TransferLineID = IN_SOH.SourceID
WHERE (WH_Transfer.TransferNo = '011724') AND (WH_Transfer.TransferDate > CONVERT(DATETIME, '2017-10-01 00:00:00', 102))
ORDER BY WH_Transfer.TransferNo, WH_TransferLines.LineNum