title: SQL query to interpolate between values
body: <p>I intend to interpolate (<strong>linear interpolation</strong>) between values in a column and insert that into a new column using a SQL query.
  Based on my search online, I suspect LEAD analytic function could be useful.
  I am new to writing SQL queries. So, any insights on how it can be achieved will be quite helpful.</p>
  
  <p>The sample data set is as described:</p>
  
  <pre><code>Emp  Test_date  Value
  ---  ---------  -----
  A    1/1/2001   null
  A    1/2/2001   100
  A    1/3/2001   null
  A    1/4/2001   80
  A    1/5/2001   null
  A    1/6/2001   null
  A    1/7/2001   75
  </code></pre>
  
  <p>The idea is to get a fourth column that has values as:</p>
  
  <pre><code>null
  100
  interpolatedValue1
  80
  interpolatedValue2
  interpolatedValue3
  75
  </code></pre>
  
  <p><em>Interpolatedvalue1</em> would be an interpolated value between <strong>100</strong> and <strong>80</strong>, </p>
  
  <p><em>Interpolatedvalue2</em> would be a linearly interpolated value between <strong>80</strong> and <strong>75</strong>.</p>
  
  <p><em>InterpolatedValue3</em> would be a linearly interpolated value between <strong>Interpolatedvalue2</strong> and <strong>75</strong></p>
  
  <p><strong><em>Here's how a simple linear interpolation works:</em></strong></p>
  
  <p>Given two points (<strong>V1</strong> at <strong>D1</strong>), (<strong>V3</strong> at <strong>D3</strong>). What would be the value <strong><em>V2</em></strong> at <strong>D2</strong>?</p>
  
  <p><strong>(V3-V1)/(D3-D1) * (D2-D1) + V1</strong></p>