View:
[AdventureWorks2016CTP3].[HumanResources].[vEmployeeDepartmentHistory]
View properties
| Name | Value |
| Schema | [HumanResources] |
| Owner | [dbo] |
| Creation date | 16.11.2015 |
| Is schema bound | |
| Encrypted | |
| ID | 1069246864 |
Creation options
| Name | Value |
| QUOTED_IDENTIFIER | ON |
| ANSI_NULLS | ON |
Resultset
| Name | Description | Data type | Max length |
| BusinessEntityID | int | 4 | |
| Title | nvarchar | 8 | |
| FirstName | [dbo].[Name] | 50 | |
| MiddleName | [dbo].[Name] | 50 | |
| LastName | [dbo].[Name] | 50 | |
| Suffix | nvarchar | 10 | |
| Shift | [dbo].[Name] | 50 | |
| Department | [dbo].[Name] | 50 | |
| GroupName | [dbo].[Name] | 50 | |
| StartDate | date | 3 | |
| EndDate | date | 3 |
Objects that [HumanResources].[vEmployeeDepartmentHistory] depends on
| Object name | Object type | Dep level |
| [HumanResources] | Schema | 1 |
| [HumanResources].[Department] | Table | 1 |
| [HumanResources].[Employee] | Table | 1 |
| [HumanResources].[EmployeeDepartmentHistory] | Table | 1 |
| [Person].[Person] | Table | 1 |
| [HumanResources].[Shift] | Table | 1 |
Extended properties
| Name | Value |
| MS_Description | Returns employee name and current and previous departments. |
SQL
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory] AS SELECT e.[BusinessEntityID] ,p.[Title] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,p.[Suffix] ,s.[Name] AS [Shift] ,d.[Name] AS [Department] ,d.[GroupName] ,edh.[StartDate] ,edh.[EndDate] FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[BusinessEntityID] = edh.[BusinessEntityID] INNER JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID] INNER JOIN [HumanResources].[Shift] s ON s.[ShiftID] = edh.[ShiftID]; GO EXEC sp_addextendedproperty N'MS_Description', N'Returns employee name and current and previous departments.', 'SCHEMA', N'HumanResources', 'VIEW', N'vEmployeeDepartmentHistory', NULL, NULL GO |
See also