View:
[AdventureWorks2016CTP3].[Sales].[vSalesPersonSalesByFiscalYears]
View properties
| Name | Value |
| Schema | [Sales] |
| Owner | [dbo] |
| Creation date | 16.11.2015 |
| Is schema bound | |
| Encrypted | |
| ID | 1229247434 |
Creation options
| Name | Value |
| QUOTED_IDENTIFIER | ON |
| ANSI_NULLS | ON |
Resultset
| Name | Description | Data type | Max length |
| SalesPersonID | int | 4 | |
| FullName | nvarchar | 152 | |
| JobTitle | nvarchar | 50 | |
| SalesTerritory | [dbo].[Name] | 50 | |
| 2002 | money | 8 | |
| 2003 | money | 8 | |
| 2004 | money | 8 |
Objects that [Sales].[vSalesPersonSalesByFiscalYears] depends on
| Object name | Object type | Dep level |
| [Sales] | Schema | 1 |
| [HumanResources].[Employee] | Table | 1 |
| [Person].[Person] | Table | 1 |
| [Sales].[SalesOrderHeader] | Table | 1 |
| [Sales].[SalesPerson] | Table | 1 |
| [Sales].[SalesTerritory] | Table | 1 |
Extended properties
| Name | Value |
| MS_Description | Uses PIVOT to return aggregated sales information for each sales representative. |
SQL
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO 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; GO EXEC sp_addextendedproperty N'MS_Description', N'Uses PIVOT to return aggregated sales information for each sales representative.', 'SCHEMA', N'Sales', 'VIEW', N'vSalesPersonSalesByFiscalYears', NULL, NULL GO |
See also