Solved

AdventureWorks2012 SQL Query Current Employee Position

Posted on 2013-10-26
4
2,434 Views
Last Modified: 2017-01-26
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
0
Comment
Question by:Jeff Darling
  • 2
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39603451
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
 
LVL 12

Author Comment

by:Jeff Darling
ID: 39603754
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
 
LVL 12

Author Closing Comment

by:Jeff Darling
ID: 39603755
Excellent job. thanks for looking at this on short notice.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39604422
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question