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];
GO
EXEC sp_addextendedproperty N'MS_Description', N'Returns employee name and current and previous departments.', 'SCHEMA', N'HumanResources', 'VIEW', N'vEmployeeDepartmentHistory', NULL, NULL
GO