title: tsql - match up to closest number in lookup
body: <p>I have a select which returns a duration value.  what i would like to do is look up this value in a lookup table and return a value for yield which is closest to this duration value.  </p>
  
  <p>e.g</p>
  
  <p>i have a duration of 0.20 for USD so from my look up table below which is derived from this code:</p>
  
  <pre><code>select fld1_val, mrkt_cap_wght from dw_iss_indx_cnstnt AS ai WITH (NOLOCK)
  
  INNER JOIN  dw_issue_dg AS i WITH (NOLOCK)
    ON ai.indx_instr_id = i.instr_id 
    AND ai.as_of_dte &gt; getdate()-2
  
  INNER JOIN dw_issue_alt_id AS ia WITH (NOLOCK)
    on ia.instr_id = ai.indx_instr_id
    AND id_ctxt_typ = 'Bloomberg ID'
    AND denom_curr_cde = 'USD'
  </code></pre>
  
  <p>this returns:</p>
  
  <pre><code>fld1_val    mrkt_cap_wght
  0.08        5.0168
  0.25        5.03
  0.5         5.09
  1           5.21
  2           5.2
  5           5.51
  10          5.67
  12          5.69
  15          5.7
  20          5.71
  </code></pre>
  
  <p>So what i would do is lookup against this and because 0.20 is closest to 0.25 i would return 5.03 as my yield.</p>
  
  <p>However i am unsure of how to do this as the look up is not looking for an exact match, just a closest to value.  Any idea how i could do this?</p>