See The Employee Salary Amount on a Given Date

Question

Does anyone know what a query would look like to see what a certain employee’s salary is at a certain date?

The table of the salary adjustment history of each employee has the following fields:

MATRICULA DATA    SALARIO_ANTERIOR    REAJUSTE    NOME    ADMISSAO_DATA   SALARIO_FIXO
000283  01.08.2009  4729.59 270.53  FULANO DA SILVA 13.07.2009  5982.48
000283  01.07.2010  5000.12 274.51  FULANO DA SILVA 13.07.2009  5982.48
000283  01.08.2011  5274.63 369.22  FULANO DA SILVA 13.07.2009  5982.48
000283  01.08.2012  5643.85 338.63  FULANO DA SILVA 13.07.2009  5982.48

The ADMISSAO_DATA and SALARIO_FIXO fields are from the Employees table I made a Join between the Readjustment Histories tables with Employees where generated the above result. The SALARIO_FIXO field is from the Employees table where you always store the current salary after the last adjustment. In the above result is 5982.48 after the adjustment made on 01.08.2012 which previously was 5643.85.

How would I consult, for example what was the salary value of this employee enrollment 000283 on 01.05.2012? I could not develop a query to check and compare to see how much the salary was on this desired date.

Can someone help me?

---

Sorack, it’s all right?

I passed this query to Mysql Bank but the last query record cannot calculate the value of the current salary after the last adjustment. It looks exactly like this in the image below, arrow the initial value before the readjustment and the date is set the first date before the readjustment, but when the query runs in SQL Fiddle correctly.

The right result should come out like this:

Answer 1

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