HumanResources.vEmployeeDepartment (view)

From dbscript Online Help

Contents 1 wikibot 1.1 Source 1.2 References 2 automatically generated

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

automatically generated

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

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

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