title: SQL query to merge and sum time-periods
body: <p>I have a database table containing time-periods and amounts. Think of them as contracts with a duration and a price per day:</p>
  
  <pre><code>start      | end        | amount_per_day
  2013-01-01 | 2013-01-31 | 100
  2013-02-01 | 2013-06-30 | 200
  2013-01-01 | 2013-06-30 | 100
  2013-05-01 | 2013-05-15 | 50
  2013-05-16 | 2013-05-31 | 50
  </code></pre>
  
  <p>I would like to make a query that will display the totals for each period, i.e.:</p>
  
  <p>From 2013-01-01 to 2013-01-31, the first and third contract are active, so the total amount per day is 200. From 2013-02-01 to 2013-04-30, the second and third row are active, so the total is 300. From 2013-05-01 to 2013-05-15 the second, third and fourth row are active, so the total is 350. From 2013-05-16 to 2013-05-31 the second, third and fifth row are active, so the total is again 350. Finally, from 2013-06-01 to 2013-06-30 only the second and third are active, so the total is back to 300.</p>
  
  <pre><code>start      | end        | total_amount_per_day
  2013-01-01 | 2013-01-31 | 200
  2013-02-01 | 2013-04-30 | 300
  2013-05-01 | 2013-05-31 | 350
  2013-06-01 | 2013-06-30 | 300
  </code></pre>
  
  <p>(It is not necessary to detect that the intervals <code>2013-05-01 -&gt; 2013-05-15</code> and <code>2013-05-16 -&gt; 2013-05-31</code> have the same totals and merge them, but it would be nice).</p>
  
  <p>I would prefer a portable solution, but if it is not possible a SQL Server will work, too.</p>
  
  <p>I can make small changes to the structure of the table, so if it would make the query simpler to e.g. notate the time-periods with the end-date exclusive (so the first period would be start = 2013-01-01, end = 2013-02-01) feel free to make such suggestions.</p>