Its whole problem is because the tables seem to be modeled in a way too complicated for the problem that must be solved. I suggest that, if possible, a table be created salario that has the value, beginning of the duration and value of the salary, so the problem will be easily solved. If you cannot change the structure, use the following query to extract the expected values:
SELECT x.matricula,
x.tipo,
x.salario,
x.de,
x.ate,
x.reajuste
FROM (-- Pega apenas os primeiros salários
SELECT y1.*
FROM (SELECT 'Contratação' AS tipo,
-- Caso ainda não possua reajuste utiliza a data atual como referência de final
COALESCE(DATE_SUB(fr.data, INTERVAL 1 DAY), CURDATE()) AS ate,
f.admissao_data AS de,
-- Caso não possua registro de reajuste utiliza o salario_fixo
COALESCE(fr.salario_anterior, f.salario_fixo) AS salario,
NULL AS salario_anterior,
NULL AS reajuste,
f.matricula
FROM funcionarios f
-- LEFT para caso seja o primeiro salário do funcionário
LEFT JOIN funcionarios_reajustes fr ON fr.matricula = f.matricula
-- Pega apenas os registros que não possuam anterior (primeiros)
WHERE NOT EXISTS(SELECT 1
FROM funcionarios_reajustes fr2
WHERE fr2.matricula = f.matricula
AND fr2.data < fr.data)
ORDER BY fr.data) y1
UNION
-- Demais salários
SELECT y2.*
FROM (SELECT 'Reajuste' AS tipo,
CASE @matricula
-- Se não for o último utiliza a data do próximo reajuste menos 1 dia
WHEN fr.matricula THEN DATE_SUB(@data_proximo, INTERVAL 1 DAY)
-- Se for o último utiliza a data atual para referência de valor final
ELSE CURDATE()
END AS ate,
@data_proximo := fr.data AS de,
CASE @matricula
-- Se não for o último utiliza o valor do próximo reajuste
WHEN fr.matricula THEN @salario_anterior
-- Se for o último faz o cálculo
ELSE fr.salario_anterior + fr.reajuste
END AS salario,
@salario_anterior := fr.salario_anterior AS salario_anterior,
fr.reajuste,
@matricula := fr.matricula AS matricula
FROM funcionarios_reajustes fr
ORDER BY fr.matricula, fr.data DESC) y2
) x
WHERE x.matricula = '000283'
AND STR_TO_DATE('15/07/2011', '%d/%m/%Y') BETWEEN x.de AND x.ate
ORDER BY x.matricula,
x.de
The previous query:
- Take the value of the oldest record to know which employee’s first salary;
- Merge the record with the calculated ones;
- Gather the data ordered by the newest dates first;
- Uses control variables that are filled in the previous record (i.e., newer). Thus the current salary is filled and the effective date;
- In the result set the filter is performed by the desired date.
Observing: I suggest you create a VIEW with the query above to facilitate legibility and re-use.
See working on SQL Fiddle.
If you want to follow the direction of creating another table, the suggestion would be the following:
CREATE TABLE IF NOT EXISTS salario(
codigo INT AUTO_INCREMENT,
matricula VARCHAR(6),
salario DECIMAL(15, 2),
inicio DATE,
CONSTRAINT pk_salario PRIMARY KEY(codigo),
CONSTRAINT fk_salario_funcionarios FOREIGN KEY(matricula) REFERENCES funcionarios(matricula)
);
And for popular:
INSERT INTO salario(matricula,
salario,
inicio)
SELECT x.matricula,
x.salario,
x.de
FROM (-- Pega apenas os primeiros salários
SELECT y1.*
FROM (SELECT 'Contratação' AS tipo,
-- Caso ainda não possua reajuste utiliza a data atual como referência de final
COALESCE(DATE_SUB(fr.data, INTERVAL 1 DAY), CURDATE()) AS ate,
f.admissao_data AS de,
-- Caso não possua registro de reajuste utiliza o salario_fixo
COALESCE(fr.salario_anterior, f.salario_fixo) AS salario,
NULL AS salario_anterior,
NULL AS reajuste,
f.matricula
FROM funcionarios f
-- LEFT para caso seja o primeiro salário do funcionário
LEFT JOIN funcionarios_reajustes fr ON fr.matricula = f.matricula
-- Pega apenas os registros que não possuam anterior (primeiros)
WHERE NOT EXISTS(SELECT 1
FROM funcionarios_reajustes fr2
WHERE fr2.matricula = f.matricula
AND fr2.data < fr.data)
ORDER BY fr.data) y1
UNION
-- Demais salários
SELECT y2.*
FROM (SELECT 'Reajuste' AS tipo,
CASE @matricula
-- Se não for o último utiliza a data do próximo reajuste menos 1 dia
WHEN fr.matricula THEN DATE_SUB(@data_proximo, INTERVAL 1 DAY)
-- Se for o último utiliza a data atual para referência de valor final
ELSE CURDATE()
END AS ate,
@data_proximo := fr.data AS de,
CASE @matricula
-- Se não for o último utiliza o valor do próximo reajuste
WHEN fr.matricula THEN @salario_anterior
-- Se for o último faz o cálculo
ELSE fr.salario_anterior + fr.reajuste
END AS salario,
@salario_anterior := fr.salario_anterior AS salario_anterior,
fr.reajuste,
@matricula := fr.matricula AS matricula
FROM funcionarios_reajustes fr
ORDER BY fr.matricula, fr.data DESC) y2
) x
WHERE x.matricula = '000283'
AND STR_TO_DATE('15/07/2011', '%d/%m/%Y') BETWEEN x.de AND x.ate
ORDER BY x.matricula,
x.de
The query made by me is like this:
SELECT fr.matricula, fr.data, fr.salario_anterior, fr.reajuste, f.nome, f.admissao_data, f.salario_fixo
FROM funcionarios_reajustes fr
JOIN funcionarios AS f ON (f.matricula = fr.matricula)
WHERE tr.matricula = 00283
ORDER BY f.nome, fr.data ASC– Jorgito da Silva Paiva
You can add one more filter in WHERE by filtering only the date.
SELECT fr.matricula, 
fr.data, 
fr.salario_anterior, 
fr.reajuste, 
f.nome, 
f.admissao_data, 
f.salario_fixo 
FROM funcionarios_reajustes fr 
JOIN funcionarios AS f 
 ON (f.matricula = fr.matricula) 
 WHERE tr.matricula = 00283 
 AND fr.data BETWEEN '20120501 00:00:00' and '20120501 23:59:59'
 ORDER BY f.nome, fr.data ASC– EmanuelF
The other select to select on a given date looks like this:
SELECT
 CASE
 WHEN '01.08.2009' < data THEN salario_anterior
 WHEN '01.07.2010' < data THEN salario_anterior
 WHEN '01.08.2011' < data THEN salario_anterior
 WHEN '01.08.2012' < data THEN salario_anterior
 END AS salario
FROM (SELECT fr.matricula, fr.data, fr.salario_anterior, fr.reajuste, f.nome, f.admissao_data, f.salario_fixo
FROM funcionarios_reajustes fr
JOIN funcionarios AS t ON (f.matricula = fr.matricula))
WHERE matricula = 00283 AND data = '01.05.2012'– Jorgito da Silva Paiva
@Jorgitodasilvapaiva made an edit in my reply to fix problems with respect to multiple registrations. Please check.
– Sorack