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];
GO
EXEC sp_addextendedproperty N'MS_Description', N'Sales representiatives (names and addresses) and their sales-related information.', 'SCHEMA', N'Sales', 'VIEW', N'vSalesPerson', NULL, NULL
GO