Difference between revisions of "HumanResources.vEmployeeDepartment (view)"

From dbscript Online Help
Jump to: navigation, search
 
Line 45: Line 45:
 
| Select
 
| Select
 
| Table
 
| Table
| [[HumanResources.Department_(_(table))|HumanResources.Department]]
+
| [[HumanResources.Department_(table)|HumanResources.Department]]
  
 
|- valign="top"
 
|- valign="top"
 
| Select
 
| Select
 
| Table
 
| Table
| [[HumanResources.Employee_(_(table))|HumanResources.Employee]]
+
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
  
 
|- valign="top"
 
|- valign="top"
 
| Select
 
| Select
 
| Table
 
| Table
| [[HumanResources.EmployeeDepartmentHistory_(_(table))|HumanResources.EmployeeDepartmentHistory]]
+
| [[HumanResources.EmployeeDepartmentHistory_(table)|HumanResources.EmployeeDepartmentHistory]]
  
 
|- valign="top"
 
|- valign="top"
 
| Select
 
| Select
 
| Table
 
| Table
| [[Person.Contact_(_(table))|Person.Contact]]
+
| [[Person.Contact_(table)|Person.Contact]]
  
 
|- valign="top"
 
|- valign="top"
 
| Schema
 
| Schema
 
| Schema
 
| Schema
| [[HumanResources_(_(schema))|HumanResources]]
+
| [[HumanResources_(schema)|HumanResources]]
  
 
|}
 
|}

Latest revision as of 23:16, 23 June 2010

wikibot[edit]

View HumanResources.vEmployeeDepartment
Description Returns employee name, title, and current department.

Source[edit]

CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[EmployeeID] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,e.[Title] AS [JobTitle] 
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[EmployeeID] = edh.[EmployeeID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());

References[edit]

Dependency Type Object Type Referenced Object
Select Table HumanResources.Department
Select Table HumanResources.Employee
Select Table HumanResources.EmployeeDepartmentHistory
Select Table Person.Contact
Schema Schema HumanResources


automatically generated[edit]

view HumanResources.vEmployeeDepartment
Description Returns employee name, title, and current department.

CREATE VIEW [HumanResources].[vEmployeeDepartment] 
AS 
SELECT 
    e.[EmployeeID] 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
    ,e.[Title] AS [JobTitle] 
    ,d.[Name] AS [Department] 
    ,d.[GroupName] 
    ,edh.[StartDate] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh 
    ON e.[EmployeeID] = edh.[EmployeeID] 
    INNER JOIN [HumanResources].[Department] d 
    ON edh.[DepartmentID] = d.[DepartmentID] 
WHERE GETDATE() BETWEEN edh.[StartDate] AND ISNULL(edh.[EndDate], GETDATE());


Dependency Type Object Type Referenced Object
Select Table HumanResources.Department
Select Table HumanResources.Employee
Select Table HumanResources.EmployeeDepartmentHistory
Select Table Person.Contact
Schema Schema HumanResources