SELECT TOP 5 C.LastName, C.FirstName, E.BusinessEntityID, SUM(SOH.SubTotal) AS SaleAmount
FROM Sales.SalesPerson SP INNER JOIN
HumanResources.Employee E ON SP.BusinessEntityID = E.BusinessEntityID INNER JOIN
Person.Person C ON E.BusinessEntityID = C.BusinessEntityID INNER JOIN
Sales.SalesOrderHeader SOH ON SP.BusinessEntityID = SOH.SalesPersonID INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
Production.Product P ON SOD.ProductID = P.ProductID INNER JOIN
Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN
Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (PC.ProductCategoryID = @ProductCategory) AND (PS.ProductSubcategoryID IN (@ProductSubcategory)) AND
(SOH.OrderDate > @StartDate) AND (SOH.OrderDate < @EndDate)
GROUP BY C.LastName, C.FirstName, E.BusinessEntityID, PC.ProductCategoryID, PS.ProductSubcategoryID
ORDER BY SUM(SOH.SubTotal) DESC |