title: Calculate Price For Overlapping Date Range
body: <p>I have a simple table named HotelRate</p>
  
  <pre><code>HID  |  START_DATE  |   END_DATE    |   PRICE_PER_DAY
  --------------------------------------
  1        01/1/2015       10/1/2015       100
  1        11/1/2015       20/1/2015       75
  1        21/1/2015       30/1/2015       110
  </code></pre>
  
  <p>what is the most simple way to calculate price for Hotel Room if user queries for Total Price between <code>5/1/2015</code> to <code>25/1/2015</code>.</p>
  
  <p>I have checked : </p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/16944258/how-can-i-query-for-overlapping-date-ranges">How can I query for overlapping date ranges?</a></li>
  <li><a href="https://stackoverflow.com/questions/3667668/overlapping-date-range-mysql">Overlapping date range MySQL</a></li>
  </ul>
  
  <p>but none of it is making much sense to me.</p>
  
  <p>I have tried a couple queries but those seems like hitting arrow in the blind. Can someone suggest me a simple and elegant way to do it ?</p>
  
  <hr>
  
  <p>@JamesZ</p>
  
  <p>On running the first query i get</p>
  
  <pre><code>start_date end_date   duration    price_per_day
  ---------- ---------- ----------- -------------
  2015-01-01 2015-01-10 5           100
  2015-01-11 2015-01-20 9           75
  2015-01-21 2015-01-30 4           110
  </code></pre>
  
  <p>For first range <code>5</code> is OK, second range it should be <code>10</code>, third be <code>5</code></p>
  
  <p>How days are calculated : Total no of nights between <code>start</code> &amp; <code>end</code> date, which is same as days difference</p>
  
  <pre><code>05-Jan-15   06-Jan-15   1 Night
  06-Jan-15   07-Jan-15   1 Night
  07-Jan-15   08-Jan-15   1 Night
  08-Jan-15   09-Jan-15   1 Night
  09-Jan-15   10-Jan-15   1 Night
  10-Jan-15   11-Jan-15   1 Night
  11-Jan-15   12-Jan-15   1 Night
  12-Jan-15   13-Jan-15   1 Night
  13-Jan-15   14-Jan-15   1 Night
  14-Jan-15   15-Jan-15   1 Night
  15-Jan-15   16-Jan-15   1 Night
  16-Jan-15   17-Jan-15   1 Night
  17-Jan-15   18-Jan-15   1 Night
  18-Jan-15   19-Jan-15   1 Night
  19-Jan-15   20-Jan-15   1 Night
  20-Jan-15   21-Jan-15   1 Night
  21-Jan-15   22-Jan-15   1 Night
  22-Jan-15   23-Jan-15   1 Night
  23-Jan-15   24-Jan-15   1 Night
  24-Jan-15   25-Jan-15   1 Night
                 Count : 20 Night
  </code></pre>