alexbancheva

Task10_Missing Parts_Exam27June2020

Feb 10th, 2021
630
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Task 10 Missing Parts
  2. WITH CTE_ActiveJobsPartsQty (PartId, Quantity)
  3. AS
  4. (
  5.     SELECT pn.PartId, SUM(pn.Quantity)
  6.         FROM Jobs AS j
  7.         JOIN PartsNeeded AS pn ON j.JobId = pn.JobId
  8.         --JOIN Parts AS p ON pn.PartId = p.PartId
  9.         WHERE j.[Status] NOT LIKE 'Finished'
  10.         GROUP BY pn.PartId
  11. ),
  12.  
  13. CTE_PendingOrdersQty (PartId, Quantity)
  14. AS
  15. (
  16.     SELECT p.PartId, SUM(op.Quantity)
  17.         FROM Parts AS p
  18.         JOIN OrderParts AS op ON p.PartId = op.PartId
  19.         JOIN Orders AS o ON op.OrderId = o.OrderId
  20.         WHERE o.Delivered = 0
  21.         GROUP BY p.PartId
  22. )
  23.  
  24. SELECT p.PartId, p.[Description], ajp.Quantity AS [Required], p.StockQty AS [In Stock],
  25.     ISNULL(poq.Quantity, 0) AS Ordered
  26.         FROM CTE_ActiveJobsPartsQty AS ajp
  27.         JOIN Parts AS p ON ajp.PartId = p.PartId
  28.         LEFT JOIN CTE_PendingOrdersQty AS poq ON p.PartId = poq.PartId
  29.         WHERE ajp.Quantity > p.StockQty + ISNULL(poq.Quantity, 0)
  30. GO
RAW Paste Data