View: [AdventureWorks2016CTP3].[HumanResources].[vEmployeeDepartment]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [HumanResources] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Is schema bound   
 Encrypted   
 ID   1053246807 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Resultset


Name  Description Data type Max length
BusinessEntityID   int 4
Title   nvarchar 8
FirstName   [dbo].[Name] 50
MiddleName   [dbo].[Name] 50
LastName   [dbo].[Name] 50
Suffix   nvarchar 10
JobTitle   nvarchar 50
Department   [dbo].[Name] 50
GroupName   [dbo].[Name] 50
StartDate   date 3
Total: 10 column(s)

Collapse image Objects that [HumanResources].[vEmployeeDepartment] depends on


Object name Object type Dep level
[HumanResources] Schema 1
[HumanResources].[Department] Table 1
[HumanResources].[Employee] Table 1
[HumanResources].[EmployeeDepartmentHistory] Table 1
[Person].[Person] Table 1
Total 5 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Returns employee name, title, and current department.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

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

Collapse image See also


List of views