AdventureWorks - Sales.vSalesPerson

AdventureWorks

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

Field	Type	Nulls?
SalesPersonID	int	No
Title	nvarchar (8)	Yes
FirstName	nvarchar (50)	No
MiddleName	nvarchar (50)	Yes
LastName	nvarchar (50)	No
Suffix	nvarchar (10)	Yes
JobTitle	nvarchar (50)	No
Phone	nvarchar (25)	Yes
EmailAddress	nvarchar (50)	Yes
EmailPromotion	int	No
AddressLine1	nvarchar (60)	No
AddressLine2	nvarchar (60)	Yes
City	nvarchar (30)	No
StateProvinceName	nvarchar (50)	No
PostalCode	nvarchar (15)	No
CountryRegionName	nvarchar (50)	No
TerritoryName	nvarchar (50)	Yes
TerritoryGroup	nvarchar (50)	Yes
SalesQuota	money	Yes
SalesYTD	money	No
SalesLastYear	money	No

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