Select C.ComponentID, ComponentsAllocated.Allocated, ComponentsOnOrder.OnOrder
From ComponentTable C
LEFT OUTER JOIN
(SELECT P.ItemLinkID, SUM(IsNull(Quantity * PT.ProductionQuantity, 0)) AS Allocated
FROM ProductionItemTable P
LEFT OUTER JOIN
ProductionTable PT ON PT.ProductionID = P.LinkID
WHERE P.ItemTypeID = 1 AND PT.StatusID = 1)
AS ComponentsAllocated ON C.ComponentID = ComponentsAllocated.ItemLinkID
LEFT OUTER JOIN
(SELECT I.ComponentID, SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity, 0)) AS OnOrder
FROM PurchaseOrderItemTable I WHERE I.ItemStatusID IN (0, 2))
AS ComponentsOnOrder ON C.ComponentID = ComponentsOnOrder.ComponentID
where C.StockHolding = 1
and
(C.StockOnHand - ComponentsAllocated.Allocated) + ComponentsOnOrder.OnOrder) < C.ReorderLevel
SELECT * FROM
(
Select C.ComponentID,
(SELECT SUM(IsNull(OrderQuantity, 0)) - SUM(IsNull(ReceiveQuantity, 0))
FROM PurchaseOrderItemTable I
WHERE C.ComponentID = I.ComponentID AND I.ItemStatusID IN (0, 2)) AS ComponentsOnOrder,
(SELECT SUM(IsNull(Quantity * PT.ProductionQuantity, 0))
FROM ProductionItemTable P LEFT OUTER JOIN
ProductionTable PT ON PT.ProductionID = P.LinkID
WHERE C.ComponentID = P.ItemLinkID AND P.ItemTypeID = 1 AND PT.StatusID = 1) AS ComponentsAllocated
From ComponentTable C
where C.StockHolding = 1
) DATA
WHERE (C.StockOnHand - ComponentsAllocated) + ComponentsOnOrder < C.ReorderLevel
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
15 Experts available now in Live!