title: Calculate price given standard rates and seasonal date ranges
body: <p>I'm  building a reservation style system.  The rooms have a yearly standard rate (this is where my question differs from other similar ones, because others have unique ranges) then they also have date ranges within the year which are set at different prices. I'm looking to calculate the price in MYSQL but when the user selects dates which are within the exceptional dates ranges, it's summing that price but also summing the standard price for those dates, meaning the total is too high.  This is getting a bit wordy so here's the query and an example:</p>
  
  <pre><code>SELECT SUM(Price * (1 + DATEDIFF(LEAST(End_date, '2015-07-25' - INTERVAL 1 DAY), GREATEST(Start_date, '2015-07-15')))) AS Total
  FROM room_rates
  WHERE roomId = '46' AND (
         '2015-07-25' - INTERVAL 1 DAY BETWEEN Start_date AND End_date 
      OR '2015-07-15'                  BETWEEN Start_date AND End_date
  )
  </code></pre>
  
  <pre class="lang-none prettyprint-override"><code>+--------+------------+------------+------------+------+
  | RoomId | Range Name | Start_Date |  End_Date  | Rate |
  +--------+------------+------------+------------+------+
  |   46   |  Standard  | 2015-01-01 | 2015-12-31 | 100  |
  +--------+------------+------------+------------+------+
  |   46   |   Summer   | 2015-07-20 | 2015-08-31 | 150  |
  +--------+------------+------------+------------+------+
  |   46   |  Christmas | 2015-12-18 | 2015-12-31 | 180  |
  +--------+------------+------------+------------+------+
  </code></pre>
  
  <p>If the user selects 2015-07-15 to 2015-07-25 as in the example in my query, what I want is to calculate as so:</p>
  
  <ul>
  <li>2015-07-15 ... 2015-07-19 at Standard rate (100 per night)</li>
  <li>2015-07-20 ... 2015-07-24 at Summer rate (150 per night)</li>
  </ul>
  
  <p>Which should total 1250. <strong>However</strong> because the Standard rate has dates between the first and last day of the year, the query is also including that price and summing rates from that range during the summer dates, meaning I get all 10 dates charged at standard rate, plus the 5 nights at summer rates, totalling 1750.</p>
  
  <p>So my question is, how can I amend the query to only use the standard rate if no alternative is available? The standard rates are always called "Standard" so I can easily identify them, I just don't know what change to make!</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I should add, I'm looking to do this inside PHP (I'm using PDO)</p>
  
  <p><strong>2nd Edit</strong>
  Also worth noting, the date ranges will always fall inside a single year, and no date ranges can overlap (with the exception of the standard rate which takes up the entire year)</p>
  
  <p><strong>SOLUTION</strong></p>
  
  <p>Decided to change my method and go with the answer suggested by a couple of people and build up a day by day database.  Here is my final, working query.  Thanks for everyone's help and suggestions.</p>
  
  <pre><code>SELECT standard_rates.villaId as `villaId`,
              sum(IFNULL(custom_rates.nightly_rate_usd, standard_rates.nightly_rate_usd))
          AS `Rate`
          FROM dates
  
            LEFT JOIN
              villa_price_bands AS standard_rates
              ON standard_rates.Name = 'Standard'
              AND dates.date BETWEEN standard_rates.Start_Date AND standard_rates.End_Date
              AND FIND_IN_SET(standard_rates.villaId, :resultIds)
  
            LEFT JOIN
              villa_price_bands AS custom_rates
              ON custom_rates.Name != 'Standard'
              AND dates.date BETWEEN custom_rates.Start_Date AND custom_rates.End_Date
              AND custom_rates.villaId = standard_rates.villaId
  
          WHERE dates.date &gt;= :arrDate
          AND dates.date &lt; :deptDate
          GROUP BY villaId
  </code></pre>