title: Php/MySQL: Hours reported, hourly rate
body: <p>I have the following tables (simplified):</p>
  
  <pre><code>   hours               hour_rates
   - user_id            - user_id
   - date               - date
   - hours              - hourly_rate
  </code></pre>
  
  <p>Hours table example:</p>
  
  <pre><code>1 - 2012-03-19 - 8
  </code></pre>
  
  <p>This means that user with id=1, at 2012-03-19 worked 8 hours in total.</p>
  
  <p>The hourly rate for a person can change in time, so I have the second table:</p>
  
  <p>hour_rates table  Example</p>
  
  <pre><code>1 - 2011-12-01 - 20
  1 - 2011-12-20 - 25
  </code></pre>
  
  <p>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$.</p>
  
  <p>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).</p>
  
  <p>Can I calculate this simply mysql side?
  If not, how to do it in an efficient way?</p>