Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT q.PartId,
- q.Description,
- q.NeededQty AS [Required],
- q.StockQty AS [In Stock],
- '0' AS Ordered
- FROM(
- SELECT j.JobId,
- p.PartId,
- p.Description,
- pn.Quantity AS NeededQty,
- p.StockQty,
- op.Quantity,
- o.Delivered,
- j.STATUS FROM Parts AS p
- LEFT JOIN OrderParts AS op ON op.PartId = p.PartId
- LEFT JOIN PartsNeeded AS pn ON pn.PartId = p.PartId
- LEFT JOIN Jobs AS j ON j.JobId = pn.JobId
- LEFT JOIN Orders AS o ON o.JobId = j.JobId
- WHERE j.STATUS <> 'Finished'
- OR ((p.StockQty - pn.Quantity) < 0 )
- AND Delivered = 0) AS q
- WHERE q.Delivered IS NULL
- AND q.StockQty - q.NeededQty < 0
- ORDER BY q.PartId
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement