Solved

AdventureWorks2012 SQL Query Current Employee Position

Posted on 2013-10-26
4
2,484 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 13

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 13

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

752 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