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
GO
EXEC sp_addextendedproperty N'MS_Description', N'Returns employee name, title, and current department.', 'SCHEMA', N'HumanResources', 'VIEW', N'vEmployeeDepartment', NULL, NULL
GO