HumanResources.vEmployeeDepartmentHistory View

Description

Returns employee name and current and previous departments.

Properties

Creation Date	08/01/2010 08:41
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
EmployeeID	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	datetime	4
EndDate	datetime	4

Objects that HumanResources.vEmployeeDepartmentHistory depends on

Database Object	Object Type	Description	Dep Level
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
HumanResources.Department	Table	Lookup table containing the departments within the Adventure Works Cycles company.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
HumanResources.EmployeeDepartmentHistory	Table	Employee department transfers.	1
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2
HumanResources.Shift	Table	Work shift lookup table.	1

View Definition

CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory] 
AS 

SELECT 
e.[EmployeeID] 
,c.[Title] 
,c.[FirstName] 
,c.[MiddleName] 
,c.[LastName] 
,c.[Suffix] 
,s.[Name] AS [Shift]
,d.[Name] AS [Department] 
,d.[GroupName] 
,edh.[StartDate] 
,edh.[EndDate]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c 
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
ON e.[EmployeeID] = edh.[EmployeeID] 
INNER JOIN [HumanResources].[Department] d 
ON edh.[DepartmentID] = d.[DepartmentID] 
INNER JOIN [HumanResources].[Shift] s
ON s.[ShiftID] = edh.[ShiftID];

Related Objects