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

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Sales.vSalesPerson |- |} <pre> CREATE VIEW [Sales].[vSalesPerson] AS SELECT ...)
 
Line 49: Line 49:
 
     ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
 
     ON cr.[CountryRegionCode] = sp.[CountryRegionCode];
 
</pre>
 
</pre>
 +
 +
== automatically generated ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|-
 +
| '''view
 +
| Sales.vSalesPerson
 +
|-
 +
 +
| '''Description
 +
| Sales representiatives (names and addresses) and their sales-related information.
 +
|-
 +
 +
|}
 +
 +
<pre>
 +
 +
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];
 +
</pre>
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Referenced Object
 +
| '''Object Type
 +
| '''Dependency Type
 +
 +
|-
 +
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 +
| Table
 +
| Select
 +
|-
 +
| [[HumanResources.EmployeeAddress_(table)|HumanResources.EmployeeAddress]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.Address_(table)|Person.Address]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Person.StateProvince_(table)|Person.StateProvince]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 +
| Table
 +
| Select
 +
|-
 +
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 +
| Table
 +
| Select
 +
|}

Revision as of 00:59, 27 November 2009

wikibot

view Sales.vSalesPerson 
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];

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


Referenced Object Object Type Dependency Type
HumanResources.Employee Table Select
HumanResources.EmployeeAddress Table Select
Person.Address Table Select
Person.Contact Table Select
Person.CountryRegion Table Select
Person.StateProvince Table Select
Sales.SalesPerson Table Select
Sales.SalesTerritory Table Select
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.vSalesPerson_(view)&oldid=1291"