HumanResources.vEmployeeDepartment View

Description

Returns employee name, title, and current department.

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
JobTitle	nvarchar	50
Department	dbo.Name	50
GroupName	dbo.Name	50
StartDate	datetime	4

Objects that HumanResources.vEmployeeDepartment 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.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2
HumanResources.Shift	Table	Work shift lookup table.	2

View Definition

CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 

SELECT 
e.[EmployeeID] 
,c.[Title] 
,c.[FirstName] 
,c.[MiddleName] 
,c.[LastName] 
,c.[Suffix] 
,e.[Title] AS [JobTitle] 
,d.[Name] AS [Department] 
,d.[GroupName] 
,edh.[StartDate] 
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] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());

Related Objects