title: Most recent employee position in date period
body: <p>I'm trying to accomplish 2 things from the following data:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>EMPLOYEEID</th>
  <th>POSITION</th>
  <th>DATEFROM</th>
  <th>DATETO</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0760891</td>
  <td>Associate Director</td>
  <td>2014-10-28 00:00:00.000</td>
  <td>2018-05-11 00:00:00.000</td>
  </tr>
  <tr>
  <td>0760891</td>
  <td>Executive Director</td>
  <td>2018-05-14 00:00:00.000</td>
  <td>NULL</td>
  </tr>
  <tr>
  <td>1048137</td>
  <td>Assistant Director</td>
  <td>2019-07-08 00:00:00.000</td>
  <td>2021-07-12 00:00:00.000</td>
  </tr>
  <tr>
  <td>1048137</td>
  <td>Coordinator</td>
  <td>2021-07-13 00:00:00.000</td>
  <td>NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>We use an evaluation year that runs from May to April (5/1 - 4/30). So, for each EMPLOYEEID, I need output that looks like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>EMPLOYEEID</th>
  <th>Position</th>
  <th>Evaluation Year</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0760891</td>
  <td>Associate Director</td>
  <td>2015</td>
  </tr>
  <tr>
  <td>0760891</td>
  <td>Associate Director</td>
  <td>2016</td>
  </tr>
  <tr>
  <td>0760891</td>
  <td>Associate Director</td>
  <td>2017</td>
  </tr>
  <tr>
  <td>0760891</td>
  <td>Associate Director</td>
  <td>2018</td>
  </tr>
  <tr>
  <td>0760891</td>
  <td>Associate Director</td>
  <td>2019</td>
  </tr>
  <tr>
  <td>0760891</td>
  <td>Executive Director</td>
  <td>2020</td>
  </tr>
  <tr>
  <td>0760891</td>
  <td>Executive Director</td>
  <td>2021</td>
  </tr>
  <tr>
  <td>0760891</td>
  <td>Executive Director</td>
  <td>2022</td>
  </tr>
  <tr>
  <td>1048137</td>
  <td>Assistant Director</td>
  <td>2020</td>
  </tr>
  <tr>
  <td>1048137</td>
  <td>Assistant Director</td>
  <td>2021</td>
  </tr>
  <tr>
  <td>1048137</td>
  <td>Assistant Director</td>
  <td>2022</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>First requirement: &quot;Fill in&quot; a Position and Evaluation Year row, per EMPLOYEEID, for any evaluation years between DATEFROM and DATETO.</p>
  <p>Second requirement: Return only the <em>last</em> active position per EMPLOYEEID and Evaluation Year in cases where the DATETO and DATEFROM values for two positions overlap within an evaluation year.</p>
  <p>The issue I'm having is that I can't seem to get the most recent (last) position in situations where -- e.g. in the case of EMPLOYEEID 1048137 here -- there are two positions &quot;active&quot; in the same evaluation year (either begins or ends in the evaluation year). That employee's Evaluation Year 2022 value should be Coordinator, not Assistant Director, but the code I've written is producing the output pictured in the second table above. It seems like I should be able to employ ROW_NUMBER to get what I need, but I'm struggling trying to implement it correctly (if, in fact, I need to implement it at all for this issue).</p>
  <p>SQL Server 2014.</p>
  <p>Thanks so much for your kind attention.</p>