Sales.vSalesPerson View

Description

Sales representiatives (names and addresses) and their sales-related information.

Properties

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

Columns

Column Name	Datatype	Length
BusinessEntityID	Integer	4
Title	VarWChar	8
FirstName	dbo.Name	50
MiddleName	dbo.Name	50
LastName	dbo.Name	50
Suffix	VarWChar	10
JobTitle	VarWChar	50
PhoneNumber	dbo.Phone	25
PhoneNumberType	dbo.Name	50
EmailAddress	VarWChar	50
EmailPromotion	Integer	4
AddressLine1	VarWChar	60
AddressLine2	VarWChar	60
City	VarWChar	30
StateProvinceName	dbo.Name	50
PostalCode	VarWChar	15
CountryRegionName	dbo.Name	50
TerritoryName	dbo.Name	50
TerritoryGroup	VarWChar	50
SalesQuota	Currency	8
SalesYTD	Currency	8
SalesLastYear	Currency	8

Objects that Sales.vSalesPerson depends on

Database Object	Object Type	Description	Dep Level
Person.Address	Table	Street address information for customers, employees, and vendors.	1
Person.AddressType	Table	Types of addresses stored in the Address table.	2
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	2
Person.BusinessEntityAddress	Table	Cross-reference table mapping customers, vendors, and employees to their addresses.	1
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	1
Person.EmailAddress	Table	Where to send a person email.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.Flag	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	1
Person.PersonPhone	Table	Telephone number and type of a person.	1
dbo.Phone	User Defined Data Type		2
Person.PhoneNumberType	Table	Type of phone number of a person.	1
Sales.SalesPerson	Table	Sales representative current information.	1
Sales.SalesTerritory	Table	Sales territory lookup table.	1
Person.StateProvince	Table	State and province lookup table.	1

View Definition

CREATE VIEW [Sales].[vSalesPerson] 
AS 

SELECT 
    s.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,[TerritoryName] = st.[Name]
    ,[TerritoryGroup] = st.[Group]
    ,s.[SalesQuota]
    ,s.[SalesYTD]
    ,s.[SalesLastYear]
FROM [Sales].[SalesPerson] s
    INNER JOIN [HumanResources].[Employee] e 
    ON e.[BusinessEntityID] = s.[BusinessEntityID]
    INNER JOIN [Person].[Person] p
    ON p.[BusinessEntityID] = s.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = s.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Sales].[SalesTerritory] st 
    ON st.[TerritoryID] = s.[TerritoryID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON ea.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];

Related Objects

Sales Schema
AdventureWorks Database