Sales.vSalesPersonSalesByFiscalYears View

Description

Uses PIVOT to return aggregated sales information for each sales representative.

Properties

Creation Date	27/10/2017 14:33
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
SalesPersonID	Integer	4
FullName	VarWChar	152
JobTitle	VarWChar	50
SalesTerritory	dbo.Name	50
2002	Currency	8
2003	Currency	8
2004	Currency	8

Objects that Sales.vSalesPersonSalesByFiscalYears depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		2
Person.Address	Table	Street address information for customers, employees, and vendors.	2
Sales.CreditCard	Table	Customer credit card information.	2
Sales.Currency	Table	Lookup table containing standard ISO currencies.	3
Sales.CurrencyRate	Table	Currency exchange rates.	2
Sales.Customer	Table	Current customer information. Also see the Person and Store tables.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	3
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
dbo.OrderNumber	User Defined Data Type		2
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	1
Sales.SalesOrderHeader	Table	General sales order information.	1
Sales.SalesPerson	Table	Sales representative current information.	1
Sales.SalesTerritory	Table	Sales territory lookup table.	1
Purchasing.ShipMethod	Table	Shipping company lookup table.	2
Person.StateProvince	Table	State and province lookup table.	3
Sales.Store	Table	Customers (resellers) of Adventure Works products.	3
dbo.ufnGetAccountingEndDate	User Defined Function	Scalar function used in the uSalesOrderHeader trigger to set the starting account date.	2
dbo.ufnGetAccountingStartDate	User Defined Function	Scalar function used in the uSalesOrderHeader trigger to set the ending account date.	2
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	3
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	2
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	2

View Definition

CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears] 
AS 

SELECT 
    pvt.[SalesPersonID]
    ,pvt.[FullName]
    ,pvt.[JobTitle]
    ,pvt.[SalesTerritory]
    ,pvt.[2002]
    ,pvt.[2003]
    ,pvt.[2004] 
FROM (SELECT 
        soh.[SalesPersonID]
        ,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName]
        ,e.[JobTitle]
        ,st.[Name] AS [SalesTerritory]
        ,soh.[SubTotal]
        ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
    FROM [Sales].[SalesPerson] sp 
        INNER JOIN [Sales].[SalesOrderHeader] soh 
        ON sp.[BusinessEntityID] = soh.[SalesPersonID]
        INNER JOIN [Sales].[SalesTerritory] st 
        ON sp.[TerritoryID] = st.[TerritoryID] 
        INNER JOIN [HumanResources].[Employee] e 
        ON soh.[SalesPersonID] = e.[BusinessEntityID] 
        INNER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = sp.[BusinessEntityID]
     ) AS soh 
PIVOT 
(
    SUM([SubTotal]) 
    FOR [FiscalYear] 
    IN ([2002], [2003], [2004])
) AS pvt;

Related Objects

Sales Schema
AdventureWorks Database