USE `test`;
DROP FUNCTION IF EXISTS `pastPosition`;
DELIMITER $$
USE `test`$$
CREATE FUNCTION `pastPosition`(`who` INT(11), `when` DATE) RETURNS int(11)
BEGIN
    DECLARE `result` int(11);
    SELECT `position_id` INTO `result`
        FROM `position_history`
        WHERE `when`>=`from` AND `when`<=`to` AND `employee_id`=`who`;
    RETURN `result`;
END$$
DELIMITER ;

CREATE  TABLE `employee` (
    `id` INT(11) UNSIGNED NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `division_id` INT(11) UNSIGNED NOT NULL
);
INSERT INTO `employee` VALUES 
    (1, 'Mrs Robinson', 2),
    (2, 'Joe DiMaggio', 2),
    (3, 'Paul Simon', 1),
    (4, 'Art Garfunkel', 1)
;

CREATE  TABLE `position` (
    `id` INT(11) UNSIGNED NOT NULL,
    `position_name` VARCHAR(50) NOT NULL,
    `is_management` TINYINT(1) NOT NULL
);
INSERT INTO `position` VALUES 
    (1, 'Grand Poobah', 1),
    (2, 'Average Poobah', 1),
    (3, 'Serf', 0)
;

CREATE  TABLE `position_history` (
    `from` DATE NOT NULL,
    `to` DATE NOT NULL,
    `employee_id` int(11) NOT NULL,
    `position_id` int(11) NOT NULL
);
INSERT INTO `position_history` VALUES 
    ('1972-01-01','1988-12-31', 3, 1),
    ('1972-01-01','1988-12-31', 4, 1),
    ('1972-01-01','1988-12-31', 1, 2),
    ('1972-01-01','1988-12-31', 2, 3)
;

CREATE  TABLE `division` (
    `id` INT(11) UNSIGNED NOT NULL,
    `name` VARCHAR(50) NOT NULL
);
INSERT INTO `division` VALUES 
    (1, 'Songwriter'),
    (2, 'Song Character')
;

