title: SQL Server join query to get row with latest date closest to another date
body: <p>I have looked through a lot of pages and can't find quite what I want to achieve. I have a select statement that I want to get the latest date that it is greater than so for example:</p>
  
  <p>If I have two dates of 2015/06/01, 2015/07/01 and in my table I have one record with the date 2015/05/01 and another with the date 2015/06/20.</p>
  
  <p>For the 2015/06/01 I want to have the max date that it is greater than in this case would be 2015/05/01. </p>
  
  <p>For the 2015/07/01, I want to have the max date that it is greater than, in this case it would be 2015/06/20.</p>
  
  <p>I want only the one option to return not all the matches which is what I currently get. </p>
  
  <p>There are other joins etc to other tables and it would use all of them but for this purpose I have taken that information out because it is irrelevant to what I'm trying to achieve. If you require any more information just comment.</p>
  
  <p>After comments I have created a Fiddle db structure, <a href="http://sqlfiddle.com/#!6/2738f/2/0" rel="nofollow">http://sqlfiddle.com/#!6/2738f/2/0</a>.</p>
  
  <p>So here is my current query:</p>
  
  <pre><code>With IptBandRates as (
      select 
      ibr.ipt_band_code,
      ibr.ipt_rate,
      ibr.concessionary_date,
      max(ibr.concessionary_date) as concessionary_date2
      from product.ipt_band_rates ibr
      inner join quote.quote qu on '4' = qu.quote_id 
      where ibr.concessionary_date &lt;= qu.[effective_date]
      group by ibr.concessionary_date, ibr.ipt_rate, ipt_band_code
  )
  
  select 
  
  IptBandRates.ipt_rate as ipt_rate
  
  from quote.premium
  inner join quote.cover on quote.premium.cover_id = quote.cover.cover_id
  inner join quote.quote qu on quote.cover.quote_id = qu.quote_id 
  
  inner join product.premium_class on
   quote.cover.product_code = product.premium_class.product_code and
   quote.cover.product_ver_no = product.premium_class.product_ver_no and
   quote.cover.class_type_code = product.premium_class.class_type_code
  
   inner join product.ipt_band on  
   product.premium_class.ipt_band_code = product.ipt_band.ipt_band_code 
  
   inner join IptBandRates on 
   product.ipt_band.ipt_band_code = IptBandRates.ipt_band_code
   inner join product.ipt_band_rates ibrs on 
   ibrs.ipt_band_code = IptBandRates.ipt_band_code and
   ibrs.concessionary_date = IptBandRates.concessionary_date
  
  where qu.quote_id  = '4'
  </code></pre>