MySQL - How do I inner join sorting the joined data
- by Gary
I'm trying to write a report which will join a person, their work, and their hourly wage at the time of work. I cannot seem to figure out the best way to join the person's cost when the date is less than the date of the work.
Let's say a person cost $30 per hour at the start of the year then got a $10 raise o Feb 5 and another on Mar 1.
01/01/2010 $30.00 (per hour)
02/05/2010 $40.00
03/01/2010 $45.00
The person put in hours several days which span the rasies.
01/05/2010 10 hours (should be at $30/hr)
01/27/2010 5 hours (again at $30)
02/10/2010 10 hours (at $40/hr)
03/03/2010 5 hours (at $45/hr)
I'm trying to write one SQL statement which will pull the hours, the cost per hour, and the hours*cost. The cost is the hourly rate last entered into the system so the cost date is less than the work date, ordered by cost date limit 1.
SELECT person.id, person.name, work.hours, person_costs.value, work.hours * person_costs.value AS value
FROM person
INNER JOIN work ON (person.id = work.person_id)
INNER JOIN person_costs ON (person.id = person_costs.person_id AND person_costs.date < work.date)
WHERE person.id = 1234
ORDER BY work.date ASC
The problem I'm having, the person_costs isn't ordered by date in descending order. It's pulling out "any" value (naturally sorted by record position) which matches the condition. How do I select the first person_cost value which is older than the work date?
Thanks!
