Owner: Sales 
Schema bound:  
Creation Date: 04/26/2006 
Modification Date: 04/26/2006 
Description: Uses PIVOT to return aggregated sales information for each sales representative. 


Name Data Type Length NULL IsGUID Description
  SalesPersonID  int       
  FullName  nvarchar  152       
  Title  nvarchar  50       
  SalesTerritory  Name  100       
  2002  money       
  2003  money       
  2004  money       
Total: 7 column(s)

Objects that [Sales].[vSalesPersonSalesByFiscalYears] depends on

Object Name Owner Object Type Dep Level
  AccountNumber  dbo  User Defined type 
  Flag  dbo  User Defined type 
  Name  dbo  User Defined type 
  NameStyle  dbo  User Defined type 
  OrderNumber  dbo  User Defined type 
  Phone  dbo  User Defined type 
  ufnLeadingZeros  dbo  Function 
  Contact  Person  Table 
  CountryRegion  Person  Table 
  CreditCard  Sales  Table 
  Currency  Sales  Table 
  SalesTerritory  Sales  Table 
  ShipMethod  Purchasing  Table 
  CurrencyRate  Sales  Table 
  Customer  Sales  Table 
  Employee  HumanResources  Table 
  StateProvince  Person  Table 
  Address  Person  Table 
  SalesPerson  Sales  Table 
  SalesOrderHeader  Sales  Table 
Total: 20 objects


CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]
        ,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName]
        ,st.[Name] AS [SalesTerritory]
        ,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
    FOR [FiscalYear]
    IN ([2002], [2003], [2004])
) AS pvt;

See Also

List of views