View: vSalesPersonSalesByFiscalYears

Properties

Property	Value
Name	vSalesPersonSalesByFiscalYears
ID	1995154153
Schema	Sales
Owner	dbo
Encrypted
Schema Bound
With Check Option
ANSI NULLS
Uses Quoted Identifier
Has Opaque Metadata
Published
Schema Published
Replicated
Has Replication Filter
Has Unchecked Assembly Data
Date Correlation View
Uses Database Collation
Tracked by CDC
MS Shipped
Modify Date	8/8/2010 5:23:46 PM
Create Date	8/8/2010 5:23:46 PM

Extended Properties

Name	Value
MS_Description	Uses PIVOT to return aggregated sales information for each sales representative.

View Columns

Column Name	Datatype	Nullable	Is Identity
SalesPersonID	int
FullName	nvarchar(152)
JobTitle	nvarchar(50)
SalesTerritory	Name
2002	money
2003	money
2004	money

Indexes

No view indexes defined.

Object Level Permissions

No object level permissions defined.

Column Level Permissions

No explicit column permissions granted to this object

Objects that depend on [vSalesPersonSalesByFiscalYears]

Sales.vSalesPersonSalesByFiscalYears

Objects on which [vSalesPersonSalesByFiscalYears] depends

Sales.vSalesPersonSalesByFiscalYears
- HumanResources.Employee
  - dbo.Flag
  - Person.Person
    - dbo.Name
    - dbo.NameStyle
    - Demographics.exist
    - Person.AdditionalContactInfoSchemaCollection
    - Person.BusinessEntity
    - Person.IndividualSurveySchemaCollection
- Sales.SalesOrderHeader

SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON 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; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Uses PIVOT to return aggregated sales information for each sales representative.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'VIEW',@level1name=N'vSalesPersonSalesByFiscalYears'

Script

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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;
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Uses PIVOT to return aggregated sales information for each sales representative.' , @level0type=N'SCHEMA',@level0name=N'Sales', @level1type=N'VIEW',@level1name=N'vSalesPersonSalesByFiscalYears'