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

From dbscript Online Help
Jump to: navigation, search
 
(5 intermediate revisions by the same user not shown)
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
−
|-
+
|- valign="top"
−
| '''view
+
| '''View
 
| Sales.vSalesPerson
 
| Sales.vSalesPerson
−
|-
+
|- valign="top"
 +
| '''Description
 +
| Sales representiatives (names and addresses) and their sales-related information.
 
|}
 
|}
  
 +
=== Source ===
 
<pre>
 
<pre>
 
CREATE VIEW [Sales].[vSalesPerson]  
 
CREATE VIEW [Sales].[vSalesPerson]  
Line 49: Line 52:
 
     ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
 
     ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
 
</pre>
 
</pre>
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[HumanResources.EmployeeAddress_(table)|HumanResources.EmployeeAddress]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.Address_(table)|Person.Address]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.StateProvince_(table)|Person.StateProvince]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Sales_(schema)|Sales]]
 +
 +
|}
 +
  
 
== automatically generated ==
 
== automatically generated ==
Line 56: Line 114:
 
| '''view
 
| '''view
 
| Sales.vSalesPerson
 
| Sales.vSalesPerson
−
|-
+
|- valign="top"
−
 
 
 
| '''Description
 
| '''Description
 
| Sales representiatives (names and addresses) and their sales-related information.
 
| Sales representiatives (names and addresses) and their sales-related information.
 
|-
 
|-
−
 
 
|}
 
|}
  
Line 110: Line 166:
 
{| 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"
 +
| '''Dependency Type
 +
| '''Object Type
 
| '''Referenced Object
 
| '''Referenced Object
−
| '''Object Type
+
 
−
| '''Dependency Type
 
−
 
 
|-
 
|-
 +
| Select
 +
| Table
 
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 +
 +
|-
 +
| Select
 
| Table
 
| Table
 +
| [[HumanResources.EmployeeAddress_(table)|HumanResources.EmployeeAddress]]
 +
 +
|-
 
| Select
 
| Select
 +
| Table
 +
| [[Person.Address_(table)|Person.Address]]
 +
 
|-
 
|-
−
| [[HumanResources.EmployeeAddress_(table)|HumanResources.EmployeeAddress]]
+
| Select
 
| Table
 
| Table
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 +
|-
 
| Select
 
| Select
 +
| Table
 +
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 +
 
|-
 
|-
−
| [[Person.Address_(table)|Person.Address]]
+
| Select
 
| Table
 
| Table
−
| Select
+
| [[Person.StateProvince_(table)|Person.StateProvince]]
 +
 
 
|-
 
|-
−
| [[Person.Contact_(table)|Person.Contact]]
 
−
| Table
 
 
| Select
 
| Select
−
|-
 
−
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 
 
| Table
 
| Table
−
| Select
+
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 +
 
 
|-
 
|-
−
| [[Person.StateProvince_(table)|Person.StateProvince]]
 
−
| Table
 
 
| Select
 
| Select
−
|-
 
−
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 
 
| Table
 
| Table
−
| Select
+
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 +
 
 
|-
 
|-
−
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
+
| Schema
−
| Table
+
| Schema
−
| Select
+
| [[Sales_(schema)|Sales]]
 +
 
 
|}
 
|}

Latest revision as of 23:18, 23 June 2010

wikibot[edit]

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

Source[edit]

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[edit]

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[edit]

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
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.vSalesPerson_(view)&oldid=2685"