I have the following tables (simplified):
hours hour_rates
- user_id - user_id
- date - date
- hours - hourly_rate
Hours table example:
1 - 2012-03-19 - 8
This means that user with id=1, at 2012-03-19 worked 8 hours in total.
The hourly rate for a person can change in time, so I have the second table:
hour_rates table Example
1 - 2011-12-01 - 20
1 - 2011-12-20 - 25
So for user with id=1, we set a hourly rate of 20$ at 2011-12-01. We changed his hourly rate at 2011-12-20, to 25$.
What I want is, to calculate how much I have to pay for a given user (ex. id=1) for a given period (ex. 2012-01-01 -> 2012-02-01).
Can I calculate this simply mysql side? If not, how to do it in an efficient way?