title: MySQL: Create one row per month for data having date ranges
body: <p><strong>!Modified!</strong> </p>
  
  <p>I have a table (mySQL 5.0.x) which contains values for date ranges.</p>
  
  <pre><code>| id | link_id | type_id | value |  start | end    |
  ====================================================
  |  1 |       1 |       1 |    10 | 201111 | 201202 |
  |  2 |       1 |       2 |    20 | 201110 | 201201 |
  |  3 |       1 |       1 |   100 | 201202 | 201202 |
  |  4 |       2 |       1 |    40 | 201202 | 201203 |
  </code></pre>
  
  <p>where</p>
  
  <ul>
  <li><strong>id</strong> is the id for record</li>
  <li><strong>link_id</strong> is for linking the data to other tables</li>
  <li><strong>type_id</strong> is for determining the type of the value from other table</li>
  <li><strong>value</strong> is numerical value</li>
  <li><strong>start</strong> and <strong>end</strong> define the range (real date or integer for year and month)</li>
  </ul>
  
  <p>The trick is that I need to present total value per type and link for a given period on a monthly basis. So the result should be like this for the range 201201 - 201202:</p>
  
  <pre><code>| period | link_id | type_id | value |
  ======================================
  | 201201 |       1 |       1 |    10 |
  | 201202 |       1 |       1 |   110 |
  | 201201 |       1 |       2 |    20 |
  | 201201 |       2 |    NULL |  NULL |
  | 201202 |       2 |       1 |    40 |
  </code></pre>
  
  <p>I could use PHP to put the information like this into the database but there are some drawbacks. I have hundreds of link_ids, many type_id, and the average difference between start and end dates is 30 months so I would have tons of rows.</p>