Sales.vSalesPersonSalesByFiscalYears_(view)

== 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 ===
{{{{<nowiki>
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;
</nowiki>}}}}

=== 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"
|-
| '''view'''
|  Sales.vSalesPersonSalesByFiscalYears
|- valign="top"
| '''Description'''
| Uses PIVOT to return aggregated sales information for each sales representative.
|-
|}

{{{{<nowiki>

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;
</nowiki>}}}}


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

|}
ScrewTurn Wiki

Navigation

Sales.vSalesPersonSalesByFiscalYears_(view)
