SQL Query onorder exclude Production order

[former_member557726]

Hi, I would like to make a query that shows stock and order information about some articles. But I want to exclude the amount "onorder" and "iscommited that are related to PO, I only want to se OR. How do I do that?

SELECT T0.[ItemCode], T0.[ItemName], T1.[OnHand] - T1.[IsCommited], T1.[OnHand], T1.[IsCommited], T1.[OnOrder] FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] WHERE T1.[WhsCode] = '01' AND T0.[ItmsGrpCod] = 103 AND T0.[ItemCode] LIKE 'O%'

[Johan_H · Accepted solution]

Hi David,

You can accomplish this by specifically including purchase and sales orders instead of excluding production orders:

SELECT i.ItemCode
      ,i.OnHand
      ,ISNULL((select sum(t.OpenQty) from por1 t where t.ItemCode = i.ItemCode), 0) AS OnOrder
      ,ISNULL((select sum(t.OpenQty) from rdr1 t where t.ItemCode = i.ItemCode), 0) AS IsCommited
FROM OITM i

Regards,

Johan

[former_member557726 · Accepted solution]

Great, worked fine, Thanx