Your entire problem occurs because the tables appear to be shaped too complicated for the problem to be solved. I suggest that, if possible, a salario table be created that has the value, beginning of validity and salary value, so the problem will be easily solved. If you can not 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:
- Get the oldest registry value to find out the employee's first salary;
- Performs the merge of the registry with the calculated ones;
- Collect the data sorted by the newest dates first;
- Uses control variables that are populated in the previous record (that is, newer). Thus the current salary is filled and the effective date;
- In the result set the filter is performed by the desired date.
Note: I suggest that you create a VIEW with query above for ease of readability and reuse.
See working in SQL Fiddle .
If you want to follow the guideline of creating another table, the suggestion would be as follows:
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
