title: Best way to interpolate values in SQL
body: <p>I have a table with rate at certain date :</p>
  
  <pre><code>          Rates
  
  Id  |     Date      |  Rate
  ----+---------------+-------
   1  |   01/01/2011  |  4.5
   2  |   01/04/2011  |  3.2
   3  |   04/06/2011  |  2.4
   4  |   30/06/2011  |  5
  </code></pre>
  
  <p>I want to get the output rate base on a simple linear interpolation.</p>
  
  <p>So if I enter 17/06/2011:</p>
  
  <pre><code>Date        Rate
  ----------  -----
  01/01/2011  4.5
  01/04/2011  3.2
  04/06/2011  2.4
  17/06/2011  
  30/06/2011  5.0
  </code></pre>
  
  <p>the linear interpolation is <code>(5 + 2,4) / 2 = 3,7</code></p>
  
  <p>Is there a way to do a simple query (SQL Server 2005), or this kind of stuff need to be done in a programmatic way (C#...) ?</p>