user1551869 user1551869 - 3 months ago 24
MySQL Question

SQL to get all active records from a history table that also has future dated records

I have a status history table that also includes future dated records.

Example: employee_jobs

id | employee_id | division_id | department_id | job_id | effective_date
1 100 1 1 1 2015-01-01
2 100 1 1 2 2016-01-01
3 100 1 2 4 2017-01-01
4 200 1 3 5 2016-01-01
5 300 1 3 6 2015-01-01
6 300 1 3 7 2016-05-25


I need a preforming SQL that will show a given employee_id's current live record when given a date: Example Date = 2016-08-15

The result set should be:

id | employee_id | division_id | department_id | job_id | effective_date
2 100 1 1 2 2016-01-01
4 200 1 3 5 2016-01-01
6 300 1 3 7 2016-05-25

Answer

I guess you want records for each employee having the latest effective_date with a constraint

(effective_date must be less than or equal to a given date)

SELECT 
*
FROM 
(
    SELECT 
    *,
    IF(@sameEmployee = employee_id, @rn := @rn + 1,
         IF(@sameEmployee := employee_id, @rn := 1, @rn := 1)
     ) AS row_number
    FROM employee_jobs
    CROSS JOIN (SELECT @sameEmployee := 0, @rn := 1) var
    WHERE effective_date <= '2016-08-15'
    ORDER BY employee_id, effective_date DESC
) AS t
WHERE t.row_number = 1
ORDER BY t.employee_id