title: return nearest adjacent rows that are earlier/later by date to another table's date
body: <p>Problem: to return a single result set based on the following</p>
  <p>for each date in report table (rdate) return the latest transaction table (tdate) that is earlier or equal to rdate (plus t1 &amp; t2)  as sdate,s1,s2</p>
  <p>for each date in report table (rdate) return the earliest transaction table (tdate) that is later or equal to rdate (plus t1 &amp; t2) as edate,e1,e2</p>
  <p>Have attempted TOP 1 / APPLY / LEAD&amp; LAG code, but cannot get results as required.</p>
  <p>Any advice is appreciated. Thanks</p>
  <p><strong>Report table</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>rdate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>06/01/2021</td>
  </tr>
  <tr>
  <td>26/01/2021</td>
  </tr>
  <tr>
  <td>15/02/2021</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Transaction table</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>tdate</th>
  <th>t1</th>
  <th>t2</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>01/01/2021</td>
  <td>17</td>
  <td>6</td>
  </tr>
  <tr>
  <td>05/01/2021</td>
  <td>5</td>
  <td>9</td>
  </tr>
  <tr>
  <td>09/01/2021</td>
  <td>8</td>
  <td>12</td>
  </tr>
  <tr>
  <td>19/01/2021</td>
  <td>15</td>
  <td>11</td>
  </tr>
  <tr>
  <td>20/01/2021</td>
  <td>12</td>
  <td>8</td>
  </tr>
  <tr>
  <td>25/01/2021</td>
  <td>9</td>
  <td>1</td>
  </tr>
  <tr>
  <td>26/01/2021</td>
  <td>8</td>
  <td>17</td>
  </tr>
  <tr>
  <td>30/01/2021</td>
  <td>7</td>
  <td>6</td>
  </tr>
  <tr>
  <td>08/02/2021</td>
  <td>6</td>
  <td>21</td>
  </tr>
  <tr>
  <td>22/02/2021</td>
  <td>14</td>
  <td>5</td>
  </tr>
  <tr>
  <td>27/02/2021</td>
  <td>11</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Result required</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>rdate</th>
  <th>sdate</th>
  <th>s1</th>
  <th>s2</th>
  <th>edate</th>
  <th>e1</th>
  <th>e2</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>06/01/2021</td>
  <td>05/01/2021</td>
  <td>5</td>
  <td>9</td>
  <td>09/01/2021</td>
  <td>8</td>
  <td>12</td>
  </tr>
  <tr>
  <td>26/01/2021</td>
  <td>26/01/2021</td>
  <td>8</td>
  <td>17</td>
  <td>26/01/2021</td>
  <td>8</td>
  <td>17</td>
  </tr>
  <tr>
  <td>15/02/2021</td>
  <td>08/02/2021</td>
  <td>6</td>
  <td>21</td>
  <td>22/02/2021</td>
  <td>14</td>
  <td>5</td>
  </tr>
  </tbody>
  </table>
  </div>