Difference between revisions of "Sales.vSalesPerson (view)"

From dbscript Online Help
Jump to: navigation, search
Line 3: Line 3:
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|-
 
|-
| '''view
+
| '''View
 
| Sales.vSalesPerson
 
| Sales.vSalesPerson
 
|- valign="top"
 
|- valign="top"
Line 11: Line 11:
 
|}
 
|}
  
 +
 +
=== Source ===
 
<pre>
 
<pre>
 
CREATE VIEW [Sales].[vSalesPerson]  
 
CREATE VIEW [Sales].[vSalesPerson]  
Line 53: Line 55:
 
</pre>
 
</pre>
  
 
+
=== References ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 99: Line 101:
 
| Table
 
| Table
 
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 +
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Sales_(schema)|Sales]]
  
 
|}
 
|}
 
  
  

Revision as of 23:07, 2 February 2010

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


automatically generated

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

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


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