Task10_Missing Parts_Exam27June2020

-- Task 10 Missing Parts
WITH CTE_ActiveJobsPartsQty (PartId, Quantity)
AS
(
    SELECT pn.PartId, SUM(pn.Quantity)
        FROM Jobs AS j
        JOIN PartsNeeded AS pn ON j.JobId = pn.JobId
        --JOIN Parts AS p ON pn.PartId = p.PartId
        WHERE j.[Status] NOT LIKE 'Finished'
        GROUP BY pn.PartId
),

CTE_PendingOrdersQty (PartId, Quantity)
AS
(
    SELECT p.PartId, SUM(op.Quantity)
        FROM Parts AS p
        JOIN OrderParts AS op ON p.PartId = op.PartId
        JOIN Orders AS o ON op.OrderId = o.OrderId
        WHERE o.Delivered = 0
        GROUP BY p.PartId
)

SELECT p.PartId, p.[Description], ajp.Quantity AS [Required], p.StockQty AS [In Stock],
    ISNULL(poq.Quantity, 0) AS Ordered
        FROM CTE_ActiveJobsPartsQty AS ajp
        JOIN Parts AS p ON ajp.PartId = p.PartId
        LEFT JOIN CTE_PendingOrdersQty AS poq ON p.PartId = poq.PartId
        WHERE ajp.Quantity > p.StockQty + ISNULL(poq.Quantity, 0)
GO