Database reference - AdventureWorks2008

AdventureWorks2008 -  views -  [Sales].[vSalesPersonSalesByFiscalYears]

Description

Uses PIVOT to return aggregated sales information for each sales representative. 

View properties

namevalue
name[Sales].[vSalesPersonSalesByFiscalYears]
createdMar 19 2009 9:10PM
modifiedMar 19 2009 9:10PM 
ansi nullson 
exec ansi nullson 
quoted identifieron 
exec quoted identifieron 
schema bindingoff 
Maximum size of a single row1,036 bytes

Columns

columndatatypelengthbytesnullscomputedcomment
SalesPersonIDint104yes  no   
FullNamenvarchar(152)152608yes  no   
JobTitlenvarchar(50)50200no  no   
SalesTerritoryName50200no  no   
2002money198yes  no  what kind of blockhead would name a column with an integer??? 
2003money198yes  no  what kind of blockhead would name a column with an integer??? 
2004money198yes  no  what kind of blockhead would name a column with an integer??? 

Dependency graph

EmployeePersonSalesOrderHeaderSalesPersonSalesTerritoryNamevSalesPersonSalesByFiscalYears

Objects that [Sales].[vSalesPersonSalesByFiscalYears] depends on

nameobject typedatabaseserverlevel
EmployeetableAdventureWorks2008SPRING\KATMAI1
FlagtypeAdventureWorks2008SPRING\KATMAI2
PersontableAdventureWorks2008SPRING\KATMAI1
NameStyletypeAdventureWorks2008SPRING\KATMAI2
SalesOrderHeadertableAdventureWorks2008SPRING\KATMAI1
OrderNumbertypeAdventureWorks2008SPRING\KATMAI2
AccountNumbertypeAdventureWorks2008SPRING\KATMAI2
SalesPersontableAdventureWorks2008SPRING\KATMAI1
SalesTerritorytableAdventureWorks2008SPRING\KATMAI1

Code

CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears] 
AS 
SELECT 
    pvt.[SalesPersonID]
    ,pvt.[FullName]
    ,pvt.[JobTitle]
    ,pvt.[SalesTerritory]
    ,pvt.[2002]
    ,pvt.[2003]
    ,pvt.[2004] 
FROM (SELECT 
        soh.[SalesPersonID]
        ,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName]
        ,e.[JobTitle]
        ,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.[BusinessEntityID] = soh.[SalesPersonID]
        INNER JOIN [Sales].[SalesTerritory] st 
        ON sp.[TerritoryID] = st.[TerritoryID] 
        INNER JOIN [HumanResources].[Employee] e 
        ON soh.[SalesPersonID] = e.[BusinessEntityID] 
        INNER JOIN [Person].[Person] p
        ON p.[BusinessEntityID] = sp.[BusinessEntityID]
     ) AS soh 
PIVOT 
(
    SUM([SubTotal]) 
    FOR [FiscalYear] 
    IN ([2002], [2003], [2004])
) AS pvt;



Documentation generated by SqlSpec