View: [AdventureWorks2016CTP3].[HumanResources].[vEmployeeDepartmentHistory]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [HumanResources] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Is schema bound   
 Encrypted   
 ID   1069246864 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image 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
Total: 11 column(s)

Collapse image 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
Total 6 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Returns employee name and current and previous departments.

Collapse image 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

Collapse image See also


List of views