Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software to build an employee geography report. Double LEFT JOIN is necessary to include the CEO (Ken Sanchez) who does not have a manager.

USE AdventureWorks

GO

SELECT    

  [Name]=RTRIM(c.FirstName)+' '+ RTRIM(c.LastName)

, [Department] = d.Name

, [Manager]=ISNULL(RTRIM(pc.FirstName)+' '+ RTRIM(pc.LastName), 'CEO')

, a.City

, sp.StateProvinceCode

, sp.CountryRegionCode

FROM        

            HumanResources.Employee e

  LEFT JOIN HumanResources.Employee emp ON e.ManagerID = emp.EmployeeID

  LEFT JOIN Person.Contact pc ON pc.ContactID = emp.ContactID

  INNER JOIN Person.Contact c ON e.ContactID = c.ContactID

  INNER JOIN HumanResources.EmployeeAddress ea ON e.EmployeeID = ea.EmployeeID

  INNER JOIN Person.Address a ON ea.AddressID = a.AddressID

  INNER JOIN Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID

  INNER JOIN HumanResources.EmployeeDepartmentHistory edh

             ON e.EmployeeID = edh.EmployeeID

  INNER JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID

WHERE edh.EndDate is NULL

ORDER BY c.LastName, c.FirstName DESC