AdventureWorks2012 SQL Query Current Employee Position

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 position:

businessentityid,firstname, lastname, jobtitle, department name and startdate.

I'll need to get the data from 3 tables.

Person.Person
HumanResources.EmployeeDepartmentHistory
HumanResources.Department

tables, with the fields I need
Here is the code that I have come up with so far, but it is incorrect.

USE adventureworks2012 

SELECT humanresources.employeedepartmenthistory.businessentityid, 
       person.person.firstname, 
       person.person.lastname, 
       humanresources.employee.jobtitle, 
       humanresources.department.name AS DepartmentName, 
       humanresources.employeedepartmenthistory.startdate 
FROM   humanresources.employeedepartmenthistory 
       JOIN humanresources.department 
         ON humanresources.department.departmentid = 
            humanresources.employeedepartmenthistory.departmentid 
       JOIN humanresources.employee 
         ON humanresources.employee.businessentityid = 
            humanresources.employeedepartmenthistory.businessentityid 
       JOIN person.person 
         ON person.businessentityid = 
            humanresources.employeedepartmenthistory.businessentityid 

Open in new window


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.

sample output
LVL 13
Jeff DarlingDeveloper AnalystAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
one method would be to use row_number() to lodate the "most recent record" by using the order "startsate DESC":
USE adventureworks2012

SELECT
        hremphist.businessentityid
      , person.person.firstname
      , person.person.lastname
      , humanresources.employee.jobtitle
      , humanresources.department.name AS DepartmentName
      , hremphist.startdate
FROM (
                SELECT
                        businessentityid
                      , departmentid
                      , startdate
                      , ROW_NUMBER() OVER (PARTITION BY businessentityid ORDER BY startdate DESC) AS rn
                FROM humanresources.employeedepartmenthistory
        ) AS hremphist
        JOIN humanresources.department
                ON humanresources.department.departmentid = hremphist.departmentid
        JOIN humanresources.employee
                ON humanresources.employee.businessentityid = hremphist.businessentityid
        JOIN person.person
                ON person.businessentityid = hremphist.businessentityid
WHERE hremphist.rn = 1
;

Open in new window

0
 
Jeff DarlingDeveloper AnalystAuthor Commented:
Thank you. That is exactly what I needed!  

I see how the relationship between the rn and the start date works by looking at the inline query that you created.

SELECT
                        businessentityid
                      , departmentid
                      , startdate
                      , ROW_NUMBER() OVER (PARTITION BY businessentityid ORDER BY startdate DESC) AS rn
                FROM humanresources.employeedepartmenthistory

Open in new window


Here is the output.  I see how you sorted by startdate DESC which has RN in ascending, then just get where RN=1

output sample 2
0
 
Jeff DarlingDeveloper AnalystAuthor Commented:
Excellent job. thanks for looking at this on short notice.
0
 
PortletPaulfreelancerCommented:
Thnaks. Yes, it is the order by used that produces the wanted rn value. DESC will ive us "the latest record", ASC would give us the earliest record.

Cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.