HumanResources.vEmployeeDepartmentHistory View

Description

Returns employee name and current and previous departments.

Properties

Creation Date	17/07/2014 16:11
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
BusinessEntityID	Integer	4
Title	VarWChar	8
FirstName	dbo.Name	50
MiddleName	dbo.Name	50
LastName	dbo.Name	50
Suffix	VarWChar	10
Shift	dbo.Name	50
Department	dbo.Name	50
GroupName	dbo.Name	50
StartDate	VarWChar	10
EndDate	VarWChar	10

Objects that HumanResources.vEmployeeDepartmentHistory depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	2
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
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	1
HumanResources.Shift	Table	Work shift lookup table.	1

View Definition

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];

Related Objects