On Hand Qty Help

[LDR]

I have a problem with a SQL statement that I have been working on. I need to see the on hand qty/weight of what I have left in storage. What I for got to take in to account was items that do not have any shipping records again them. So when I run this it only shows me the qty/weight of items that had shipping records against them. How do I get this to show the QTY/weight of items that has no adjustments also?

Thanks!


SELECT dbo.tblInboundConversions.BOLUID, dbo.tblInboundConversions.Consignee, dbo.tblInboundConversions.BOL, dbo.tblInboundConversions.Marks,
dbo.tblInboundConversions.InQTY, dbo.tblOutboundQTY.OutQTY, dbo.tblInboundConversions.InQTY - dbo.tblOutboundQTY.OutQTY AS OnHandQTY,
dbo.tblInboundConversions.InWeightPerUnit,
dbo.tblInboundConversions.InWeightPerUnit * (dbo.tblInboundConversions.InQTY - dbo.tblOutboundQTY.OutQTY) AS OnHandWeightMT,
dbo.tblInboundConversions.InWeightPerUnit * (dbo.tblInboundConversions.InQTY - dbo.tblOutboundQTY.OutQTY) * 1.1 AS StorageCost,
dbo.tblInboundConversions.CompletionDate, dbo.tblInboundConversions.VesselName
FROM dbo.tblInboundConversions INNER JOIN
dbo.tblOutboundQTY ON dbo.tblInboundConversions.BOLUID = dbo.tblOutboundQTY.BOLUID AND
dbo.tblInboundConversions.BOL = dbo.tblOutboundQTY.BOL AND dbo.tblInboundConversions.Marks = dbo.tblOutboundQTY.Marks
WHERE (dbo.tblInboundConversions.InQTY - dbo.tblOutboundQTY.OutQTY > 0) AND (dbo.tblInboundConversions.CompletionDate = CONVERT(DATETIME,
'2004-09-12 00:00:00', 102)) AND (dbo.tblInboundConversions.Consignee = N'CustomerX')

 

[SBendBuckeye]

Hello,

Depending on which table you need to see, change your INNER JOIN to either a LEFT JOIN or a RIGHT JOIN based on answering this question:

If you want to see everything in dbo.tblInboundConversions and ONLY those items in dbo.tblOutboundQTY which match then change it to a LEFT JOIN.

If you want to see everything in dbo.tblOutboundQTY and ONLY those items in dbo.tblInboundConversions which match then change it to a RIGHT JOIN. Good Luck!

Have a great day!

j2consulting@yahoo.com