Execute the following script in Query Editor to create a transaction report for frame products in 2004:

USE AdventureWorks;

WITH cteTransactionHistory (ProductID, OrderQuantity) AS
(
SELECT ProductID, Sum(Quantity)
FROM Production.TransactionHistory
WHERE Year(TransactionDate) = 2004
GROUP BY ProductID
)
SELECT
[Products: Frame, 2004] = pp.Name,
cte.ProductID,
cte.OrderQuantity,
ProductModel = pm.Name,
SubCategory = ps.Name
FROM Production.Product pp
INNER JOIN cteTransactionHistory AS cte
ON cte.ProductID = pp.ProductID
INNER JOIN Production.ProductModel pm
ON pm.ProductModelID = pp.ProductModelID
INNER JOIN Production.ProductSubcategory ps
ON pp.ProductSubCategoryID = ps.ProductSubCategoryID
WHERE pp.Name like ('%frame%')
AND pp.ProductModelID IS NOT NULL
ORDER BY pp.Name

GO