View: [AdventureWorks2016CTP3].[Sales].[vSalesPerson]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [Sales] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Is schema bound   
 Encrypted   
 ID   1213247377 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Resultset


Name  Description Data type Max length
BusinessEntityID   int 4
Title   nvarchar 8
FirstName   [dbo].[Name] 50
MiddleName   [dbo].[Name] 50
LastName   [dbo].[Name] 50
Suffix   nvarchar 10
JobTitle   nvarchar 50
PhoneNumber   [dbo].[Phone] 25
PhoneNumberType   [dbo].[Name] 50
EmailAddress   nvarchar 50
EmailPromotion   int 4
AddressLine1   nvarchar 60
AddressLine2   nvarchar 60
City   nvarchar 30
StateProvinceName   [dbo].[Name] 50
PostalCode   nvarchar 15
CountryRegionName   [dbo].[Name] 50
TerritoryName   [dbo].[Name] 50
TerritoryGroup   nvarchar 50
SalesQuota   money 8
SalesYTD   money 8
SalesLastYear   money 8
Total: 22 column(s)

Collapse image Objects that [Sales].[vSalesPerson] depends on


Object name Object type Dep level
[Sales] Schema 1
[Person].[Address] Table 1
[Person].[BusinessEntityAddress] Table 1
[Person].[CountryRegion] Table 1
[Person].[EmailAddress] Table 1
[HumanResources].[Employee] Table 1
[Person].[Person] Table 1
[Person].[PersonPhone] Table 1
[Person].[PhoneNumberType] Table 1
[Sales].[SalesPerson] Table 1
[Sales].[SalesTerritory] Table 1
[Person].[StateProvince] Table 1
Total 12 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Sales representiatives (names and addresses) and their sales-related information.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

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

Collapse image See also


List of views