View: HumanResources.vEmployeeDepartment

Properties

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

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
JobTitle	nvarchar	50
Department	Name	100
GroupName	Name	100
StartDate	datetime	8

Total: 10 column(s)

Objects that [HumanResources].[vEmployeeDepartment] 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].[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());