Welcome Guest, you are in: <root> • Login

Page History: HumanResources vEmployeeDepartment (view)

Compare Page Revisions

3 2 1 0 Current 3 2 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: 2010/06/24 01:08

wikibot ¶

View	HumanResources.vEmployeeDepartment
Description	Returns employee name, title, and current department.

Source ¶

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());

References ¶

Dependency Type	Object Type	Referenced Object
Select	Table	HumanResources.Department
Select	Table	HumanResources.Employee
Select	Table	HumanResources.EmployeeDepartmentHistory
Select	Table	Person.Contact
Schema	Schema	HumanResources