View: HumanResources.vEmployeeDepartmentHistory

Properties

Owner:	HumanResources
Schema bound:
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	Returns employee name and current and previous departments.

Columns

Name	Data Type	Length	NULL	IsGUID	Description
EmployeeID	int	4
Title	nvarchar	8
FirstName	Name	100
MiddleName	Name	100
LastName	Name	100
Suffix	nvarchar	10
Shift	Name	100
Department	Name	100
GroupName	Name	100
StartDate	datetime	8
EndDate	datetime	8

Total: 11 column(s)

Objects that [HumanResources].[vEmployeeDepartmentHistory] depends on

Object Name	Owner	Object Type	Dep Level
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
NameStyle	dbo	User Defined type	1
Phone	dbo	User Defined type	1
Contact	Person	Table	2
Department	HumanResources	Table	2
Shift	HumanResources	Table	2
Employee	HumanResources	Table	3
EmployeeDepartmentHistory	HumanResources	Table	4

Total: 9 objects

SQL

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