HumanResources.vEmployeeDepartmentHistory

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	HumanResources
Name	vEmployeeDepartmentHistory
Module	Human Resources

Returns employee name and current and previous departments.

Columns

		Name	Data type	References
	1	BusinessEntityID	int
	2	Title	nvarchar(8)	HumanResources.Shift
References: HumanResources.Shift
	3	FirstName	nvarchar(50)
	4	MiddleName	nvarchar(50)
	5	LastName	nvarchar(50)
	6	Suffix	nvarchar(10)
	7	Shift	nvarchar(50)
	8	Department	nvarchar(50)
	9	GroupName	nvarchar(50)
	10	StartDate	date
	11	EndDate	date

Relations

Foreign table		Primary table	Join	Title / Name / Description
HumanResources.vEmployeeDepartmentHistory		HumanResources.Shift	HumanResources.vEmployeeDepartmentHistory.Title = HumanResources.Shift.Name	fk_Shift_vEmployeeDepartmentHistory xcbxcvbxcvbbvbvb
HumanResources.vEmployeeDepartmentHistory.Title = HumanResources.Shift.Name Name: fk_Shift_vEmployeeDepartmentHistory xcbxcvbxcvbbvbvb

Unique keys

	Key name	Columns	Description
	fsadfasf	LastName	xcbvxcvbvbxcvbxcvbbb
xcbvxcvbvbxcvbxcvbbb

Uses

Name
HumanResources.vEmployeeDepartmentHistory
HumanResources.Department
HumanResources.Employee
HumanResources.Employee
HumanResources.EmployeeDepartmentHistory
HumanResources.Shift
Person.Person
HumanResources.Shift

Script

CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory] AS SELECT e.[BusinessEntityID] ,p.[Title] ,p.[FirstName] ,p.[MiddleName] ,p.[LastName] ,p.[Suffix] ,s.[Name] AS [Shift] ,d.[Name] AS [Department] ,d.[GroupName] ,edh.[StartDate] ,edh.[EndDate] 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] INNER JOIN [HumanResources].[Shift] s ON s.[ShiftID] = edh.[ShiftID];

CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory] 
AS 
SELECT 
    e.[BusinessEntityID] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
    ,p.[Suffix] 
    ,s.[Name] AS [Shift]
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
    ,edh.[EndDate]
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] 
    INNER JOIN [HumanResources].[Shift] s
    ON s.[ShiftID] = edh.[ShiftID];

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