title: SQL Server - How to join with max value from second table and apply specific condition
body: <p>I have two tables:
  Weeks</p>
  
  <pre><code>| WeekID | StartDate  |
  |  1     | 2016-12-25 |
  |  2     | 2017-01-01 |
  |  3     | 2017-01-08 |
  </code></pre>
  
  <p>and Settings</p>
  
  <pre><code>| ID | SettingVal | ApplyFrom  |
  |  1 | 10         | 2016-06-01 |
  |  2 | 13         | 2017-01-01 |
  |  3 | 5          | 2017-01-02 |
  </code></pre>
  
  <p>For each WeekID, I need to select SettingVal with MAX(ApplyFrom) existing, but also ApplyFrom &lt;= DATEADD(day, 6, StartDate) from table Weeks, for example:</p>
  
  <pre><code>| WeekID | SettingVal |
  |  1     | 10         |
  |  2     | 5          |
  |  3     | 5          |
  </code></pre>
  
  <p>When I write the following query:</p>
  
  <pre><code>SELECT t1.WeekID, t2.SettingVal
  FROM Weeks t1
  LEFT OUTER JOIN Settings t2 ON t2.ApplyFrom &lt;= DATEADD(day, 6, t1.StartDate)
  </code></pre>
  
  <p>it joins one row from first table with multiple rows from second table. How do I join only with a row having MAX(ApplyFrom), and select the SettingVal column I need?</p>