Description
Uses PIVOT to return aggregated sales information for each sales representative.
View properties
| name | value |
|---|
| name | [Sales].[vSalesPersonSalesByFiscalYears] |
| created | Mar 19 2009 9:10PM |
| modified | Mar 19 2009 9:10PM
|
| ansi nulls | on
|
| exec ansi nulls | on
|
| quoted identifier | on
|
| exec quoted identifier | on
|
| schema binding | off
|
| Maximum size of a single row | 1,036 bytes |
Columns
| column | datatype | length | bytes | nulls | computed | comment |
|---|
| SalesPersonID | int | 10 | 4 | yes
| no
| |
| FullName | nvarchar(152) | 152 | 608 | yes
| no
| |
| JobTitle | nvarchar(50) | 50 | 200 | no
| no
| |
| SalesTerritory | Name | 50 | 200 | no
| no
| |
| 2002 | money | 19 | 8 | yes
| no
| what kind of blockhead would name a column with an integer???
|
| 2003 | money | 19 | 8 | yes
| no
| what kind of blockhead would name a column with an integer???
|
| 2004 | money | 19 | 8 | yes
| no
| what kind of blockhead would name a column with an integer???
|
Dependency graph
Objects that [Sales].[vSalesPersonSalesByFiscalYears] depends on
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;