HumanResources.vEmployeeDepartmentHistory View

Description

Returns employee name and current and previous departments.

Properties

Creation Date	27/10/2017 14:33
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	DBDate	8
EndDate	DBDate	8

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