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