CODEWITHSUNDEEP
sqlselect
user2425635
user2425635

Reputation: 11

SQL select Employees with Hire Status on specified date from history table

I have an Employees table and an EmploymentHistory table that captures changes to their employment status. I want to be able to select a list of all employees that had a "hired" status for a given date (e.g. 04/02/2013, which would only yield John Smith below).

Employees       
employeeId    lastName    firstName
1             Smith       John
2             Doe         Jane
3             Carson      Mike

EmployeeHistory         
employeeHistoryId   employeeId    employmentStatus    transactionDate
1                   1             Hired               3/1/2013
2                   2             Candidate           4/1/2013
3                   2             Not Hired           4/5/2013
4                   3             Candidate           1/1/2013
5                   3             Hired               1/3/2013
6                   3             Terminated          2/15/2013

Thanks!

Upvotes: 1

Views: 2658

Answers (1)

Andomar
Andomar

Reputation: 238296

If your database supports row_number(), you can use it to find the last row per employee before a particular date. For example:

select  *
from    Employees e
join    (
        select  row_number() over (
                    partition by employeeId
                    order by transactionDate desc) as rn
        ,       employeeId
        ,       employmentStatus
        from    EmployeeHistory
        where   transactionDate < '2013-04-02'
        ) h
on      e.EmployeeId = h.EmployeeId
        and h.rn = 1 -- Only last row
where   h.employmentStatus = 'Hired'

Live example at SQL Fiddle.

Upvotes: 1

Related Questions