employee database with varing salary over time.

Question

Hello,

I have the following tables:

PROJECTS - project_id, name
EMPLOYEES - employee_id, name
SALARY - employee_id, date, per_hour
HOURS - log_id, project_id, employee_id, date, num_hours

I need to query how much a project is costing. Problem is that Salary can vary. For example, a person can get a raise.

The SALARY table logs the per_hour charge for an employee. With every change in cost being recorded with its date.

How can I query this information to make sure that the the log from the HOURS table is always matched to the right entry from the SALARY table. Right match being.. depending on the date of the hours log, get the row from the salary table with the highest date before the log's date.

ie.. if the work was performed on Feb 14th. Get the row for this employee from the Salary table with the highest date.. but still before the 14th.

Thank you,

Answer 1

In the 'Hours' table actually log the value of the salary that you use (don't link it based on ID). This will give you more flexibility in the future.

Answer 2

I have found the easiest way to handle queries spanning dates like this is to store a StartDate and an EndDate, where the EndDate is NULL for the current salary. I use a trigger to make sure there is only ever one NULL value for EndDate, and that there are no overlapping date ranges, or gaps between the ranges. StartDate is made not nullable, since that is never a valid value.

Then your join is pretty simple:

select h.num_hours, s.per_hour
from hours h
inner join salary s on h.employee_id = s.employee_id 
    and h.date >= s.StartDate and (h.date <= s.EndDate or s.EndDate is null)

Answer 3

What you need is an end date on SALARY. When a new record is inserted into SALARY for an employee, the previous record with the highest date (or better yet, a current flag set to 'Y' as recommended by cletus) should have its end date column set to the same date as the start date for the new record.

This should work with your current schema but be aware that it may be slow.

SELECT
  SUM(h.num_hours * s.per_hour) AS cost
FROM PROJECTS p
INNER JOIN HOURS h
  ON p.project_id = h.project_id
INNER JOIN (
    SELECT
      s1.employee_id,
      s1.date AS start_date,
      MIN(s2.date) AS end_date
    FROM SALARY s1
    INNER JOIN SALARY s2
      ON s1.employee_id = s2.employee_id
      AND s1.date < s2.date
    GROUP BY
      s1.employee_id,
      s1.date) s
  ON h.employee_id = s.employee_id
  AND h.date >= s.start_date
  AND h.date < s.end_date