Difference between revisions of "Sales.vSalesPersonSalesByFiscalYears (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.vSalesPersonSalesByFiscalYears
 
| Sales.vSalesPersonSalesByFiscalYears
−
|-
+
|- valign="top"
 +
| '''Description
 +
| Uses PIVOT to return aggregated sales information for each sales representative.
 
|}
 
|}
  
 +
=== Source ===
 
<pre>
 
<pre>
 
CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]  
 
CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]  
Line 43: Line 46:
 
) AS pvt;
 
) AS pvt;
 
</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
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 +
 +
|- 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 50: Line 93:
 
| '''view
 
| '''view
 
| Sales.vSalesPersonSalesByFiscalYears
 
| Sales.vSalesPersonSalesByFiscalYears
−
|-
+
|- valign="top"
−
 
 
 
| '''Description
 
| '''Description
 
| Uses PIVOT to return aggregated sales information for each sales representative.
 
| Uses PIVOT to return aggregated sales information for each sales representative.
 
|-
 
|-
−
 
 
|}
 
|}
  
Line 98: Line 139:
 
{| 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
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 +
|-
 
| Select
 
| Select
 +
| Table
 +
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 +
 
|-
 
|-
−
| [[Person.Contact_(table)|Person.Contact]]
+
| Select
 
| Table
 
| Table
−
| Select
+
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 +
 
 
|-
 
|-
−
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 
−
| 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.vSalesPersonSalesByFiscalYears
Description Uses PIVOT to return aggregated sales information for each sales representative.

Source[edit]

CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears] 
AS 
SELECT 
    pvt.[SalesPersonID]
    ,pvt.[FullName]
    ,pvt.[Title]
    ,pvt.[SalesTerritory]
    ,pvt.[2002]
    ,pvt.[2003]
    ,pvt.[2004] 
FROM (SELECT 
        soh.[SalesPersonID]
        ,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName]
        ,e.[Title]
        ,st.[Name] AS [SalesTerritory]
        ,soh.[SubTotal]
        ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
    FROM [Sales].[SalesPerson] sp 
        INNER JOIN [Sales].[SalesOrderHeader] soh 
        ON sp.[SalesPersonID] = soh.[SalesPersonID]
        INNER JOIN [Sales].[SalesTerritory] st 
        ON sp.[TerritoryID] = st.[TerritoryID] 
        INNER JOIN [HumanResources].[Employee] e 
        ON soh.[SalesPersonID] = e.[EmployeeID] 
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.ContactID 
    ) AS soh 
PIVOT 
(
    SUM([SubTotal]) 
    FOR [FiscalYear] 
    IN ([2002], [2003], [2004])
) AS pvt;

References[edit]

Dependency Type Object Type Referenced Object
Select Table HumanResources.Employee
Select Table Person.Contact
Select Table Sales.SalesOrderHeader
Select Table Sales.SalesPerson
Select Table Sales.SalesTerritory
Schema Schema Sales


automatically generated[edit]

view Sales.vSalesPersonSalesByFiscalYears
Description Uses PIVOT to return aggregated sales information for each sales representative. 

CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears] 
AS 
SELECT 
    pvt.[SalesPersonID]
    ,pvt.[FullName]
    ,pvt.[Title]
    ,pvt.[SalesTerritory]
    ,pvt.[2002]
    ,pvt.[2003]
    ,pvt.[2004] 
FROM (SELECT 
        soh.[SalesPersonID]
        ,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName]
        ,e.[Title]
        ,st.[Name] AS [SalesTerritory]
        ,soh.[SubTotal]
        ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
    FROM [Sales].[SalesPerson] sp 
        INNER JOIN [Sales].[SalesOrderHeader] soh 
        ON sp.[SalesPersonID] = soh.[SalesPersonID]
        INNER JOIN [Sales].[SalesTerritory] st 
        ON sp.[TerritoryID] = st.[TerritoryID] 
        INNER JOIN [HumanResources].[Employee] e 
        ON soh.[SalesPersonID] = e.[EmployeeID] 
        INNER JOIN [Person].[Contact] c 
        ON e.[ContactID] = c.ContactID 
    ) AS soh 
PIVOT 
(
    SUM([SubTotal]) 
    FOR [FiscalYear] 
    IN ([2002], [2003], [2004])
) AS pvt;


Dependency Type Object Type Referenced Object
Select Table HumanResources.Employee
Select Table Person.Contact
Select Table Sales.SalesOrderHeader
Select Table Sales.SalesPerson
Select Table Sales.SalesTerritory
Schema Schema Sales
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.vSalesPersonSalesByFiscalYears_(view)&oldid=2686"