Resolved: Trying to get the salary as at the last contract

Question

I was trying to getting the first name, last name and salary as of the last signed contract with the procedure below

DELIMITER $$
CREATE PROCEDURE last_contract_salary (in p_emp_no int)
BEGING
SELECT e.emp_no, e.first_name, e.last_name, s.salary, max(s.from_date) FROM employees e
JOIN salaries s on e.emp_no = s.emp_no
WHERE e.emp_no = p_emp_no;
END $$
DELIMITER ;

When I executed the procedure, it's giving me what I wanted except that it was picking the salary as of the first contract date. I tried putting HAVING after the where clause but I still got the same output.

DELIMITER $$
CREATE PROCEDURE last_contract_salary (in p_emp_no int)
BEGING
SELECT e.emp_no, e.first_name, e.last_name, s.salary FROM employees e
JOIN salaries s on e.emp_no = s.emp_no
WHERE e.emp_no = p_emp_no HAVING max(s.from_date);
END $$
DELIMITER ;

Answer 1

Hi Yinka!
Thanks for reaching out.

Actually, you do not need MAX(s.from_date), you need MAX(s.salary). Please, use the following code:
DELIMITER $$
CREATE PROCEDURE last_contract_salary (in p_emp_no int)
BEGIN
SELECT e.emp_no, e.first_name, e.last_name, MAX(s.salary) FROM employees e
JOIN salaries s on e.emp_no = s.emp_no
WHERE e.emp_no = p_emp_no``;
END $$
DELIMITER ;

Hope this helps.
Best,
Tsvetelin

Answer 2

Thanks, Tsvetelin. It helped. What if I want to get the salary as of the last contract. Let's imagine the 2019/2020 pandemic that some companies were laying of while cutting others salary and then further imaging that that was the last contract till date. How does one go about combining the last date (which is definitely the higher and MAX can be used) and the last salary which might not be the highest (hence, MAX is not an option).

Answer 3

Hello Yinka,

I think I managed to answer your concern, where the procedure would return the most recent salary based on from_date column.
First, let me show the query that returns the max salary based on from_date. I decided to include all the other fields you wanted to simplify the query in the procedure later on:

SELECT t1.emp_no, e.first_name, e.last_name, s.salary
FROM
	(SELECT emp_no, MAX(from_date) AS from_date
	FROM salaries
	GROUP BY emp_no) AS t1
JOIN
	salaries s
	ON s.emp_no = t1.emp_no
JOIN
	employees e
    ON e.emp_no = t1.emp_no
WHERE s.from_date = t1.from_date;

Then I just included the above as a Table subquery in the stored procedure:

DELIMITER $$
CREATE PROCEDURE last_contract_salary(in p_emp_no INT)
BEGIN
	SELECT *
    FROM (
		SELECT t1.emp_no, e.first_name, e.last_name, s.salary
		FROM
			(SELECT emp_no, MAX(from_date) AS from_date
			FROM salaries
			GROUP BY emp_no) AS t1
		JOIN
			salaries s
			ON s.emp_no = t1.emp_no
		JOIN
			employees e
			ON e.emp_no = t1.emp_no
		WHERE s.from_date = t1.from_date
		) AS T
	WHERE T.emp_no = p_emp_no;
END $$
DELIMITER ;

Since MAX(salary) are in the same line as the MAX(from_date) for each employee, the result would just be the same as Tsvetelin's. However, I highly recommend that you UPDATE the database to provide a data with your description (max from_date but not max salary) to check if this works.

I hope this helps,
Carl

Resolved: Trying to get the salary as at the last contract

Submit an answer

related questions