mercredi 25 novembre 2015

How To Select Manager Name From This Stored Procedure?

I am trying to get an employee manager's first and last name IF the employee has a manager ( Some employee's do not ex CEO etc ). Currently it returns the employee name for manager and if there is no ManagerID in contact it wont return any values

Here is my general table structure for the tables I'm trying to access:

Employee

EmpID

EmployeeNumber

StartDate

isManager

Status ( full time / part time )

ContactID

Contact

ContactID

Fname

Lname

ManagerID

Department

DeptID

Name

DeptHistory

DeptHistID

DeptID

PosTitle

StartDate

EndDate

ModifiedDate

And here is the query I have been manipulating:

SELECT

dh.StartDate, dh.PositionTitle, d.Name, e.EmployeeNumber, e.Classification, e.Status, c1.FirstName, c1.LastName, c1.SIN, c1.DateOfBirth, c1.PhoneNumber, c1.EmailAddress, c1.AddressLine1, c1.AddressLine2, c1.PostalCode, c1.City, c1.Province, (c2.FirstName+ ' ' + c2.LastName) AS Manager

FROM Person.Contact c1

JOIN HumanResources.Employee e ON c1.ContactID = e.ContactID

JOIN HumanResources.EmployeeDepartmentHistory dh ON e.EmpID = dh.EmpID

JOIN HumanResources.Department d ON dh.DepartmentID = d.DepartmentID

JOIN Person.Contact c2 ON c2.ManagerID = e.EmpID

WHERE

e.EmpID = @empID

Aucun commentaire:

Enregistrer un commentaire