AdventureWorks

Basic Index   Expanded Index

View Name:Sales.vSalesPerson
Description:Sales representiatives (names and addresses) and their sales-related information.

FieldTypeNulls?
SalesPersonIDintNo
Titlenvarchar (8)Yes
FirstNamenvarchar (50)No
MiddleNamenvarchar (50)Yes
LastNamenvarchar (50)No
Suffixnvarchar (10)Yes
JobTitlenvarchar (50)No
Phonenvarchar (25)Yes
EmailAddressnvarchar (50)Yes
EmailPromotionintNo
AddressLine1nvarchar (60)No
AddressLine2nvarchar (60)Yes
Citynvarchar (30)No
StateProvinceNamenvarchar (50)No
PostalCodenvarchar (15)No
CountryRegionNamenvarchar (50)No
TerritoryNamenvarchar (50)Yes
TerritoryGroupnvarchar (50)Yes
SalesQuotamoneyYes
SalesYTDmoneyNo
SalesLastYearmoneyNo

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];