Employee Sales Summary

/* ***** Source for query: EmpSalesYearOverYear ***** */

SELECT C.FirstName + ' ' + C.LastName AS Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
DATEPART(Month, SOH.OrderDate) AS OrderMonthNum, DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS Sales
FROM Sales.SalesOrderHeader SOH INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
Sales.SalesPerson SP ON SOH.SalesPersonID = SP.SalesPersonID INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
WHERE    (DATEPART(Year, SOH.OrderDate) <= @ReportYear - 1 OR
DATEPART(Year, SOH.OrderDate) = @ReportYear AND DATEPART(Month, SOH.OrderDate) <= @ReportMonth) AND
(SOH.SalesPersonID = @EmpID)
GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID, DATEPART(Year, SOH.OrderDate),
DATEPART(Month, SOH.OrderDate), DATENAME(Month, SOH.OrderDate)

/* ***** Source for query: SalesEmps ***** */

SELECT E.EmployeeID, C.FirstName + N' ' + C.LastName AS Employee
FROM HumanResources.Employee E INNER JOIN
Sales.SalesPerson SP ON E.EmployeeID = SP.SalesPersonID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
ORDER BY C.LastName, C.FirstName

/* ***** Source for query: EmpSalesMonth ***** */

SELECT C.FirstName + ' ' + C.LastName AS Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
DATEPART(Month, SOH.OrderDate) AS OrderMonthNum, DATENAME(Month, SOH.OrderDate) AS OrderMonth,
PC.Name AS ProdCat, SUM(SOD.LineTotal) AS Sales
FROM Sales.SalesOrderHeader SOH INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
Sales.SalesPerson SP ON SOH.SalesPersonID = SP.SalesPersonID INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID 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    (DATEPART(Year, SOH.OrderDate) <= @ReportYear) AND (DATEPART(Month, SOH.OrderDate) = @ReportMonth) AND
(SOH.SalesPersonID = @EmpID)
GROUP BY C.FirstName + ' ' + C.LastName, DATEPART(Year, SOH.OrderDate), DATEPART(Month, SOH.OrderDate),
DATENAME(Month, SOH.OrderDate), PC.Name

/* ***** Source for query: EmpSalesDetail ***** */

SELECT C.FirstName + ' ' + C.LastName AS Employee, DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
PS.Name AS SubCat, SUM(SOD.LineTotal) AS Sales, SOH.SalesOrderNumber, P.Name AS Product,
SUM(SOD.OrderQty) AS OrderQty, SOD.UnitPrice, PC.Name AS ProdCat
FROM Sales.SalesOrderHeader SOH INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
Sales.SalesPerson SP ON SOH.SalesPersonID = SP.SalesPersonID INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID 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    (DATEPART(Year, SOH.OrderDate) = @ReportYear) AND (DATEPART(Month, SOH.OrderDate) = @ReportMonth) AND
(SOH.SalesPersonID = @EmpID)
GROUP BY C.FirstName + ' ' + C.LastName, DATEPART(Month, SOH.OrderDate), SOH.SalesOrderNumber,
P.Name, PS.Name, SOD.UnitPrice, PC.Name