View: Sales.vSalesPersonSalesByFiscalYears

Properties

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

Columns

Name	Data Type	Length	NULL	IsGUID	Description
SalesPersonID	int	4
FullName	nvarchar	152
Title	nvarchar	50
SalesTerritory	Name	100
2002	money	8
2003	money	8
2004	money	8

Total: 7 column(s)

Objects that [Sales].[vSalesPersonSalesByFiscalYears] depends on

Object Name	Owner	Object Type	Dep Level
AccountNumber	dbo	User Defined type	1
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
NameStyle	dbo	User Defined type	1
OrderNumber	dbo	User Defined type	1
Phone	dbo	User Defined type	1
ufnLeadingZeros	dbo	Function	2
Contact	Person	Table	2
CountryRegion	Person	Table	2
CreditCard	Sales	Table	2
Currency	Sales	Table	2
SalesTerritory	Sales	Table	2
ShipMethod	Purchasing	Table	2
CurrencyRate	Sales	Table	3
Customer	Sales	Table	3
Employee	HumanResources	Table	3
StateProvince	Person	Table	3
Address	Person	Table	4
SalesPerson	Sales	Table	4
SalesOrderHeader	Sales	Table	5

Total: 20 objects

SQL

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;