title: Return a function parameter based on function result
body: <p>In my simplified example scenario, my objective is to return the sensor temperature (called "TemperatureOfRecord") that is closest to 70 degrees, given a particular timestamp and building. For example, building #1's TemperatureOfRecord for the 8:00 a.m. timestamp is 65 degrees because 65 degrees is closer to 70 degrees than 80 degrees is. The "Delta" column is a computed column that returns the absolute value of the sensor temperature minus 70 degrees. I use the differential to test each sensor temperature against the target temperature of 70 degrees.</p>
  
  <p>The nested correlated subquery below returns the correct results, but I am having a lot of trouble scaling this approach out for my actual scenario. Therefore, I would like to ask if there is a simpler approach that I could use.</p>
  
  <pre><code>SELECT
      tt.TmStamp
      , tt.Building
      , tt.Sensor
      , tt.Temperature
      , tt.Delta
      , (SELECT tt2.Temperature
          FROM dbo.TempTest tt2
          WHERE tt2.Building = tt.Building
              AND tt2.TmStamp = tt.TmStamp
              AND tt2.Delta = 
              (SELECT MIN(tt3.Delta)
              FROM dbo.TempTest tt3
              WHERE tt3.Building = tt.Building
                  AND tt3.TmStamp = tt.TmStamp)
      ) AS TemperatureOfRecord
  FROM dbo.TempTest tt
  </code></pre>
  
  <p>The results of the above query are below.</p>
  
  <p><img src="https://i.stack.imgur.com/D2Zx3.png" alt="enter image description here"></p>