Sales vSalesPersonSalesByFiscalYears (view)

Modified on 2011/08/27 11:19 by wikibot Categorized as Uncategorized

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

|}

devio dbscript ScrewTurn Wiki

Navigation¶

Sales vSalesPersonSalesByFiscalYears (view)