SELECT PER.FirstName + ' ' + PER.LastName AS Employee,
PS.Name AS Subcategory, SUM(DET.LineTotal) AS Sales,
SOH.SalesOrderID, SOH.SalesOrderNumber,
P.Name AS Product,
SUM(DET.OrderQty) AS OrderQty, DET.UnitPrice,
PC.Name AS Category
FROM Sales.SalesOrderHeader SOH
INNER JOIN [Sales].[SalesPerson] SP ON SP.[BusinessEntityID] = SOH.[SalesPersonID]
INNER JOIN Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID
INNER JOIN [HumanResources].[Employee] E ON SOH.[SalesPersonID] = E.[BusinessEntityID]
INNER JOIN [Person].[Person] PER ON PER.[BusinessEntityID] = SP.[BusinessEntityID]
INNER JOIN Production.Product P ON DET.ProductID = P.ProductID
INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (DATEPART(Year, SOH.OrderDate) = @ReportYear) AND
(DATEPART(Month, SOH.OrderDate) = @ReportMonth) AND
(SOH.SalesPersonID = @EmployeeID)
GROUP BY PER.FirstName + ' ' + PER.LastName,
DATEPART(Month, SOH.OrderDate), SOH.SalesOrderID, SOH.SalesOrderNumber,
P.Name, PS.Name, DET.UnitPrice, PC.Name |