DELIMITER $$

DROP FUNCTION IF EXISTS get_history_id_for_employee_and_date;$$

CREATE FUNCTION  get_history_id_for_employee_and_date
(
    history_type VARCHAR(20),
    emp_number INT,
    as_of_date DATE
)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN

    IF history_type = 'job' THEN
        RETURN (SELECT id FROM `ohrm_job_record_history` WHERE effective_date <= as_of_date AND employee_number = emp_number ORDER BY effective_date desc, id DESC LIMIT 1);
    END IF;

    IF history_type = 'salary' THEN
        RETURN (SELECT id FROM `ohrm_salary_record_history` WHERE effective_date <= as_of_date AND employee_number = emp_number ORDER BY effective_date desc, id DESC LIMIT 1);
    END IF;

RETURN NULL;

END;$$

DELIMITER ;
