I have a situation similar to this with real world data. I was able to reproduce the problem in the AdventureWorks2012
database and will use that data as my example.
The problem is that I want to get the current position of an employee. Some employees have held more than one position or a position in more than one department.
I need a list of the employees with their current
, lastname, jobtitle, department name and startdate.
I'll need to get the data from 3 tables.
Here is the code that I have come up with so far, but it is incorrect.
humanresources.department.name AS DepartmentName,
ON humanresources.department.departmentid =
ON humanresources.employee.businessentityid =
ON person.businessentityid =
Here is the output.
Notice that Rob Walters is listed with both departments. Rob is one of the employees that has held a position in more than 1 department. There are others too.