Welcome Guest, you are in: Login

devio dbscript ScrewTurn Wiki

RSS RSS

Navigation





Search the wiki
»

PoweredBy

Page History: Sales vSalesPersonSalesByFiscalYears (view)

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: 2010/01/09 18:09


wikibot

view Sales.vSalesPersonSalesByFiscalYears
DescriptionUses 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 TypeObject TypeReferenced Object
SelectTableHumanResources.Employee
SelectTablePerson.Contact
SelectTableSales.SalesOrderHeader
SelectTableSales.SalesPerson
SelectTableSales.SalesTerritory

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam.