DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create an employee history view?

Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to create a view and test it with a SELECT query.

-- SQL create view - SQL inner join - SQL select from view

-- SQL string concatenation - T-SQL inner join

USE AdventureWorks;

GO

 

CREATE VIEW [HumanResources].[vEmpDeptHistory]

AS

  SELECT c.[LastName] + ', ' + c.[FirstName]   AS [Name],

         cm.[LastName] + ', ' + cm.[FirstName] AS Manager,

         s.[Name]                              AS [Shift],

         d.[Name]                              AS [Department],

         d.[GroupName],

         convert(VARCHAR,edh.[StartDate],110)  AS StartDate,

         convert(VARCHAR,edh.[EndDate],110)    AS EndDate

  FROM   [HumanResources].[Employee] e

         INNER JOIN [HumanResources].[Employee] m

           ON e.ManagerID = m.EmployeeID

         INNER JOIN [Person].[Contact] c

           ON c.[ContactID] = e.[ContactID]

         INNER JOIN [Person].[Contact] cm

           ON cm.[ContactID] = m.[ContactID]

         INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh

           ON e.[EmployeeID] = edh.[EmployeeID]

         INNER JOIN [HumanResources].[Department] d

           ON edh.[DepartmentID] = d.[DepartmentID]

         INNER JOIN [HumanResources].[Shift] s

           ON s.[ShiftID] = edh.[ShiftID];

 

GO

 

-- Test view

SELECT *

FROM     [HumanResources].[vEmpDeptHistory]

ORDER BY Name

GO

/* Partial results

 

Name              Manager           Shift       Department

Abbas, Syed       Welcker, Brian    Day         Sales

Abercrombie,      Kim   Brown, Jo   Day         Production

Abolrous, Hazem   Hamilton, James   Day         Quality Assurance

Ackerman, Pilar   Krebs, Peter      Day         Shipping and Receiving

Adams, Jay        Campbell, John    Evening     Production

Ajenstat, F…      Trenary, Jean     Day         Information Services

Alberts, Amy      Welcker, Brian    Day         Sales

Alderson, Greg    Kane, Lori        Night       Production

*/

 

 

-- Cleanup

DROP VIEW [HumanResources].[vEmpDeptHistory]

GO

 

Related article:

CREATE VIEW (Transact-SQL)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE