HumanResources.vEmployeeDepartment

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	HumanResources
Name	vEmployeeDepartment
Module	Human Resources

Returns employee name, title, and current department.

Columns

	Name	Data type
1	BusinessEntityID	int
2	Title	nvarchar(8)
3	FirstName	nvarchar(50)
4	MiddleName	nvarchar(50)
5	LastName	nvarchar(50)
6	Suffix	nvarchar(10)
7	JobTitle	nvarchar(50)
8	Department	nvarchar(50)
9	GroupName	nvarchar(50)
10	StartDate	date

Relations

Foreign table		Primary table	Join	Title / Name / Description
HumanResources.Employee		HumanResources.vEmployeeDepartment		qqq asdasda
Name: qqq asdasda

Uses

Name
HumanResources.vEmployeeDepartment
HumanResources.Department
HumanResources.Employee
HumanResources.Employee
HumanResources.EmployeeDepartmentHistory
Person.Person

Used by

Name
HumanResources.vEmployeeDepartment
HumanResources.Employee

Script

CREATE VIEW [HumanResources].[vEmployeeDepartment] AS SELECT e.[BusinessEntityID] ,p.[Title] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,p.[Suffix] ,e.[JobTitle] ,d.[Name] AS [Department] ,d.[GroupName] ,edh.[StartDate] FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh ON e.[BusinessEntityID] = edh.[BusinessEntityID] INNER JOIN [HumanResources].[Department] d ON edh.[DepartmentID] = d.[DepartmentID] WHERE edh.EndDate IS NULL

CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[BusinessEntityID] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,e.[JobTitle]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee] e
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[BusinessEntityID] = edh.[BusinessEntityID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE edh.EndDate IS NULL

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56