Solved

AdventureWorks2012 SQL Query Current Employee Position

Posted on 2013-10-26
4
2,132 Views
Last Modified: 2013-10-27
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now