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

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Sales.vSalesPersonSalesByFiscalYears |- |} <pre> CREATE VIEW [Sales].[vSalesPe...)
 
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== wikibot ==
 
== wikibot ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- valign="top"
 +
| '''View
 +
| Sales.vSalesPersonSalesByFiscalYears
 +
|- valign="top"
 +
| '''Description
 +
| Uses PIVOT to return aggregated sales information for each sales representative.
 +
|}
 +
 +
=== Source ===
 +
<pre>
 +
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;
 +
</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 ==
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
Line 5: Line 93:
 
| '''view
 
| '''view
 
| Sales.vSalesPersonSalesByFiscalYears
 
| Sales.vSalesPersonSalesByFiscalYears
 +
|- valign="top"
 +
| '''Description
 +
| Uses PIVOT to return aggregated sales information for each sales representative.
 
|-
 
|-
 
|}
 
|}
  
 
<pre>
 
<pre>
 +
 
CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]  
 
CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]  
 
AS  
 
AS  
Line 43: Line 135:
 
) AS pvt;
 
) AS pvt;
 
</pre>
 
</pre>
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 +
 +
|-
 +
| Schema
 +
| Schema
 +
| [[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"