Welcome Guest, you are in: <root> • Login

Page History: Sales vSalesPerson (view)

Compare Page Revisions

3 2 1 0 Current 3 2 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: 2010/06/24 01:10

wikibot ¶

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

Source ¶

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

References ¶

Dependency Type	Object Type	Referenced Object
Select	Table	HumanResources.Employee
Select	Table	HumanResources.EmployeeAddress
Select	Table	Person.Address
Select	Table	Person.Contact
Select	Table	Person.CountryRegion
Select	Table	Person.StateProvince
Select	Table	Sales.SalesPerson
Select	Table	Sales.SalesTerritory
Schema	Schema	Sales