1
I was studying queries in mysql using the Employees demo database (link), when I decided to do a search in the table salaries than would be a report of how much the company spent per year on wages.
Table salaries:
emp_no int(11) PK
salary int(11)
from_date PK date
to_date date
from_date is the date an employee started receiving a salary and to_date is when he finished receiving it (he was fired or had a raise).
Assuming an employee received a certain salary from the year 2000 until 2005, as I inform the database that it should be included in the calculation in all years between those two?
The research that I did and that seems to have returned the result that I hope is (in part) this:
SELECT 1985 AS ano, AVG(salary) AS media FROM salaries
WHERE 1985 BETWEEN YEAR(from_date) AND YEAR(to_date)
UNION
SELECT 1986 AS ano, AVG(salary) AS media FROM salaries
WHERE 1986 BETWEEN YEAR(from_date) AND YEAR(to_date)
UNION
...
Repeating that until senior year.
Obviously this code is not good, because it repeats the same SELECT and the same search (with the different year) several times, and in this database each of these Selects in this table takes 1.5 seconds.
Does anyone know a more efficient way to do this research?
Thanks, that helped a lot. I hadn’t thought about this question that wages change in the middle of the year, now I’m going to see a way to do this research taking this into account.
– Rafael