ProductName	InStock	Purchased
Product1	746	24
Product2	732	NULL
Product3	1000	50
Product4	0	17
Product5	0	NULL
Product6	0	18

		Product1	Product2	etc
InStock		746		732
Purchased	24		NULL

create table #StockLevels
(
    CategoryID int,
    CategoryName nvarchar(50),
    CurrentStock int,
    BoughtStock int
)
--Simply get all the current stock levels
INSERT INTO #StockLevels
SELECT
    CMRC_Categories.CategoryID,
    CategoryName,
    CurrentStock,
    0

FROM
    CMRC_Categories LEFT JOIN CMRC_StockLevels
        ON CMRC_Categories.CategoryID = CMRC_StockLevels.CategoryID

GROUP BY
    CMRC_Categories.CategoryID,
    CategoryName,
    CurrentStock

--Update the temp table with only the orders which have been completed
UPDATE
    #StockLevels
SET     
    BoughtStock = 
(
    SELECT 
        SUM(dbo.CMRC_OrderDetails.Quantity * dbo.CMRC_Products.Quantity) AS BoughtStock
    FROM         dbo.CMRC_Orders INNER JOIN
                          dbo.CMRC_OrderDetails ON dbo.CMRC_Orders.OrderID = dbo.CMRC_OrderDetails.OrderID RIGHT OUTER JOIN
                          dbo.CMRC_Products ON dbo.CMRC_OrderDetails.ProductID = dbo.CMRC_Products.ProductID
    WHERE     
        (dbo.CMRC_Orders.CompletedDate IS NULL)
      AND
        (CMRC_Products.CategoryID = #StockLevels.CategoryID)
    GROUP BY 
        dbo.CMRC_Products.CategoryID
    HAVING 
        SUM(dbo.CMRC_OrderDetails.Quantity * dbo.CMRC_Products.Quantity) >0
)

SELECT 
    CategoryName, 
    CurrentStock, 
    BoughtStock 
FROM 
    #StockLevels

Drop table #StockLevels