View: Sales.vSalesPerson

Properties

Owner:	Sales
Schema bound:
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	Sales representiatives (names and addresses) and their sales-related information.

Columns

Name	Data Type	Length	NULL	IsGUID	Description
SalesPersonID	int	4
Title	nvarchar	8
FirstName	Name	100
MiddleName	Name	100
LastName	Name	100
Suffix	nvarchar	10
JobTitle	nvarchar	50
Phone	Phone	50
EmailAddress	nvarchar	50
EmailPromotion	int	4
AddressLine1	nvarchar	60
AddressLine2	nvarchar	60
City	nvarchar	30
StateProvinceName	Name	100
PostalCode	nvarchar	15
CountryRegionName	Name	100
TerritoryName	Name	100
TerritoryGroup	nvarchar	50
SalesQuota	money	8
SalesYTD	money	8
SalesLastYear	money	8

Total: 21 column(s)

Objects that [Sales].[vSalesPerson] depends on

Object Name	Owner	Object Type	Dep Level
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
NameStyle	dbo	User Defined type	1
Phone	dbo	User Defined type	1
Contact	Person	Table	2
CountryRegion	Person	Table	2
SalesTerritory	Sales	Table	2
Employee	HumanResources	Table	3
StateProvince	Person	Table	3
Address	Person	Table	4
SalesPerson	Sales	Table	4
EmployeeAddress	HumanResources	Table	5

Total: 12 objects

SQL

CREATE VIEW [Sales].[vSalesPerson]
AS
SELECT
    s.[SalesPersonID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,[JobTitle] = e.[Title]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[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.[EmployeeID] = s.[SalesPersonID]
    LEFT OUTER JOIN [Sales].[SalesTerritory] st
    ON st.[TerritoryID] = s.[TerritoryID]
    INNER JOIN [Person].[Contact] c
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea
    ON e.[EmployeeID] = ea.[EmployeeID]
    INNER JOIN [Person].[Address] a
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];