title: tsql case syntax in distance formula
body: <p>The acos function works in the interval -1 &lt;= x &lt;= 1.</p>
  
  <p>Sometimes, because of rounding, I'm getting 1.00001 to calculate the acos over, then the following where clause is giving me a floating number error:</p>
  
  <pre><code>    where
          (6371 * acos(cos(radians(@ga_where)) * cos(radians(thing_geolocation.ga_thing)) * cos(radians(thing_geolocation.go_thing) - radians(@go_where)) + sin(radians(@ga_where)) * sin(radians(thing_geolocation.ga_thing)))) &lt; 1.5
  </code></pre>
  
  <p>So, I'll have to refactor this where clause into something like this:</p>
  
  <pre><code>    where
          (
              6371 *
              acos
              (
                  case
                      when cos(radians(@latitude)) * cos(radians(thing_geolocation.ga_thing)) * cos(radians(thing_geolocation.go_thing) - radians(@longitude)) + sin(radians(@latitude)) * sin(radians(thing_geolocation.ga_thing)) &gt; 1 then 1
                      when cos(radians(@latitude)) * cos(radians(thing_geolocation.ga_thing)) * cos(radians(thing_geolocation.go_thing) - radians(@longitude)) + sin(radians(@latitude)) * sin(radians(thing_geolocation.ga_thing)) &lt; -1 then -1
                      else cos(radians(@latitude)) * cos(radians(thing_geolocation.ga_thing)) * cos(radians(thing_geolocation.go_thing) - radians(@longitude)) + sin(radians(@latitude)) * sin(radians(thing_geolocation.ga_thing))
                  end
              )
          ) &lt; 1.5 
  </code></pre>
  
  <p>But, is not sounding good (it will cause to calculate the distance 3x, right?).</p>
  
  <p>I have other ideas, like a subquery or a outerapply, but because the entiry query is very complex and the same fields are used to other purposes,  I'm trying to solve it without change the whole query.</p>
  
  <p>ps: I tried to use the point.distance function, but it doesn't work well with null values, so I choose the keep this way.</p>
  
  <p>Any ideas?</p>