title: Select Rate based on date dow
body: <p>I am trying to select 1 of 2 rate columns from a table based on the day of the week that is being searched. I am attempting to use two tables for this query: a calendar table (table name=calendar, column name=caldates) which is strictly a list of dates where each row is a date between 2012-06-30 and 2014-05-31. I also have another table (table name=rates) that has 4 columns: start_date, end_date, weekday_rate, and weekend_rate.</p>
  
  <pre><code>Start_date      end_date     weekday_rate     weekend_rate
  "2012-05-01"  "2012-06-30"        69              150
  "2012-07-01"  "2012-08-31"        74              200
  "2012-09-01"  "2012-11-14"        75              210
  "2012-11-15"  "2013-01-31"        90              150
  </code></pre>
  
  <p>When a query is performed, the system needs to look up the correct rate. Right now my query looks like this:</p>
  
  <pre><code>SELECT  
      CASE cast(extract(dow from caldates) as int)
      WHEN '0'  then (select weekday_rate from rates)
      WHEN '1'  then (select weekday_rate from rates)
      WHEN '2'  then (select weekday_rate from rates)
      WHEN '3'  then (select weekday_rate from rates)
      WHEN '4'  then (select weekday_rate from rates)
      WHEN '5'  then (select weekend_rate from rates)
      When '6'  then (select weekend_rate from rates)
      End AS the_date_rate
  FROM calendar WHERE caldates &gt;= '2012-08-30' and  caldates &lt;=  '2012-09-04' ;
  </code></pre>
  
  <p>When I run the query, I receive the error "ERROR:  more than one row returned by a subquery used as an expression. "</p>
  
  <p>I would like the output to display the range of dates as well as the rate associated with the date. The above example would look something like </p>
  
  <pre><code>caldates        the_date_rate
  2012-08-30           74
  2012-08-31           200
  2012-09-01           210
  2012-09-02           75
  2012-09-03           75
  2012-09-04           75
  </code></pre>
  
  <p>Any ideas?</p>