title: MySQL generating monthly breakdown from date ranges
body: <p>good morning,</p>
  
  <p>I have a nagging issue I cannot really solve.. I have a database table like this, showing resources spent (value) in a date range by person:</p>
  
  <pre><code>    id,name,startdate,enddate,value
      --------------------------------
      10,John,2012-01-14,2012-10-30,200000
      11,Jack,2012-02-01,2012-08-01,70000
      12,John,2012-05-01,2012-06-01,2000
  </code></pre>
  
  <p>I need a query that creates the result like this, summarizing the 'value' by month, taking partial months into account</p>
  
  <pre><code>month, name, value
  ------------------    
  2012-01, John, 9000
  2012-02, John, 18000
  2012-03, John, 18000
  2012-04, John, 18000
  2012-05, John, 20000
  2012-06, John, 18000
  2012-07, John, 18000
  2012-08, John, 18000
  2012-01, John, 18000
  2012-02, Jack, 10000
  2012-03, Jack, 10000
  2012-04, Jack, 10000
  2012-05, Jack, 10000
  2012-06, Jack, 10000
  2012-07, Jack, 10000
  2012-08, Jack, 0
  </code></pre>
  
  <p>Now I know how I'd do this procedurally (like with PHP) with a loop: get the daily amount, then check month by month how many days spent there according to the range and multiply it by the daily amount.</p>
  
  <p>thanks
  peter</p>