HumanResources.vEmployeeDepartment View

Information

NamevEmployeeDepartment
SchemaHumanResources
Created29.3.2010. 21:58:56
Modified29.3.2010. 21:58:56

SQL Script

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