SELECT P.FirstName + ' ' + P.LastName AS Employee, DATEPART(Year, SOH.OrderDate) AS Year, DATEPART(Month, SOH.OrderDate) AS MonthNumber, DATENAME(Month,
SOH.OrderDate) AS Month, SUM(DET.LineTotal) AS Sales
FROM Sales.SalesPerson AS SP INNER JOIN
Sales.SalesOrderHeader AS SOH ON SP.BusinessEntityID = SOH.SalesPersonID INNER JOIN
Sales.SalesOrderDetail AS DET ON SOH.SalesOrderID = DET.SalesOrderID INNER JOIN
Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID INNER JOIN
HumanResources.Employee AS E ON SOH.SalesPersonID = E.BusinessEntityID INNER JOIN
Person.Person AS P ON P.BusinessEntityID = SP.BusinessEntityID
WHERE (DATEPART(Year, SOH.OrderDate) <= @ReportYear - 1 OR
DATEPART(Year, SOH.OrderDate) = @ReportYear AND DATEPART(Month, SOH.OrderDate) <= @ReportMonth) AND (SOH.SalesPersonID = @EmployeeID)
GROUP BY P.FirstName + ' ' + P.LastName, SOH.SalesPersonID, DATEPART(Year, SOH.OrderDate), DATEPART(Month, SOH.OrderDate), DATENAME(Month, SOH.OrderDate) |