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


Name Data Type Length NULL IsGUID Description
  SalesPersonID  int       
  Title  nvarchar       
  FirstName  Name  100       
  MiddleName  Name  100       
  LastName  Name  100       
  Suffix  nvarchar  10       
  JobTitle  nvarchar  50       
  Phone  Phone  50       
  EmailAddress  nvarchar  50       
  EmailPromotion  int       
  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       
  SalesYTD  money       
  SalesLastYear  money       
Total: 21 column(s)

Objects that [Sales].[vSalesPerson] depends on

Object Name Owner Object Type Dep Level
  Flag  dbo  User Defined type 
  Name  dbo  User Defined type 
  NameStyle  dbo  User Defined type 
  Phone  dbo  User Defined type 
  Contact  Person  Table 
  CountryRegion  Person  Table 
  SalesTerritory  Sales  Table 
  Employee  HumanResources  Table 
  StateProvince  Person  Table 
  Address  Person  Table 
  SalesPerson  Sales  Table 
  EmployeeAddress  HumanResources  Table 
Total: 12 objects


CREATE VIEW [Sales].[vSalesPerson]
    ,[JobTitle] = e.[Title]
    ,[StateProvinceName] = sp.[Name]
    ,[CountryRegionName] = cr.[Name]
    ,[TerritoryName] = st.[Name]
    ,[TerritoryGroup] = st.[Group]
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];

See Also

List of views