title: How can I interpolate lat/long over time in SQL Server?
body: <p>I've got two tables, simplified as seen below.</p>
  
  <pre><code>CREATE TABLE value(
      Timestamp DATETIME2,
      Value float NOT NULL,
      PRIMARY KEY(Timestamp)
  );
  
  CREATE TABLE location(
      Timestamp DATETIME2,
      Position GEOMETRY NOT NULL,
      PRIMARY KEY(Timestamp)
  ); 
  </code></pre>
  
  <p>Performing a simple LEFT JOIN such as:</p>
  
  <pre><code>SELECT V.Timestamp, V.Value, L.Position 
  FROM   value V
         LEFT JOIN location L ON V.Timestamp = L.Timestamp;
  </code></pre>
  
  <p>This yields all the values but there are NULL's present where there isn't an exact match on <code>Timestamp</code>.</p>
  
  <p>My goal is to get an interpolated position for every value in the value table.  I need to somehow interpolate values in my query and this is where I'm getting stuck.  </p>
  
  <p>EDIT: I've since discovered I can add a CASE statement to check for NULL</p>
  
  <pre><code>SELECT 
      V.Timestamp, 
      V.Value, 
      CASE WHEN L.Position IS NULL THEN 
          (&lt;something clever here&gt;) 
      ELSE 
          L.Position 
      END As Position
  
  FROM value V
       LEFT JOIN location L ON V.Timestamp = L.Timestamp;
  </code></pre>