SELECT TOP 5 S.Name AS StoreName, SUM(SOH.SubTotal) AS SaleAmount, PS.ProductSubcategoryID,
PS.ProductCategoryID
FROM Production.Product P INNER JOIN
Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN
Sales.SalesOrderDetail SOD ON P.ProductID = SOD.ProductID INNER JOIN
Sales.Customer CU INNER JOIN
Sales.SalesOrderHeader SOH ON CU.CustomerID = SOH.CustomerID INNER JOIN
Sales.Store S ON CU.CustomerID = S.BusinessEntityID ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE (SOH.OrderDate > @StartDate) AND
(SOH.OrderDate < @EndDate) AND (PS.ProductCategoryID = @ProductCategory) AND (PS.ProductSubcategoryID IN (@ProductSubcategory))
GROUP BY S.Name, PS.ProductSubcategoryID, PS.ProductCategoryID
ORDER BY SUM(SOH.SubTotal) DESC |