Solved

AdventureWorks2012 SQL Query Current Employee Position

Posted on 2013-10-26
4
2,210 Views
Last Modified: 2017-01-17
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

863 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

20 Experts available now in Live!

Get 1:1 Help Now