title: Joining in SQL with Timestamp Intervals
body: <p>Consider the following tables.</p>
  
  <pre><code>Table A
  
  Id   Paket    Starttime                Endtime
  8    4714     2019-05-23 13:28:28.000  2019-05-23 13:28:31.000
  7    4713     2019-05-23 13:28:10.000  2019-05-23 13:28:28.000
  6    4712     2019-05-23 13:27:57.000  2019-05-23 13:28:10.000
  5    4711     2019-05-23 13:27:40.000  2019-05-23 13:27:57.000
  </code></pre>
  
  <pre><code>Table B
  
  Timestamp                  Bla
  2019-05-23 13:28:26.000    A
  2019-05-23 13:28:27.000    A
  2019-05-23 13:28:28.000    A
  2019-05-23 13:28:29.000    B
  2019-05-23 13:28:30.000    B
  2019-05-23 13:28:31.000    B
  2019-05-23 13:28:32.000    B
  2019-05-23 13:28:33.000    B
  </code></pre>
  
  <p>I would like to have the following result.</p>
  
  <pre><code>Result
  
  Timestamp                  Bla    Paket
  2019-05-23 13:28:26.000    A      4713
  2019-05-23 13:28:27.000    A      4713
  2019-05-23 13:28:28.000    A      4714
  2019-05-23 13:28:29.000    B      4714
  2019-05-23 13:28:30.000    B      4714
  </code></pre>
  
  <p>Some things to note: </p>
  
  <ul>
  <li>In <code>Table A</code>, the start time of one paket is the same as the end time of its predecessor. </li>
  <li>In <code>Table A</code> I don't really care if the intervals are half-open on one side or the other. For example, the time <code>2019-05-23 13:28:28.000</code> may belong to either Paket 4713 or 4714 (but not both). Either interpretation is fine. In <code>Result</code>, I have written that <code>2019-05-23 13:28:28.000</code> belongs to 4714, but I may as well have written 4713 (and then added one more line for <code>2019-05-23 13:28:31.000</code>).</li>
  <li>In <code>Result</code> there may be fewer lines than in <code>Table B</code>, because the last lines of <code>Table B</code> may not fall into any of the Start-Endtime Intervals from <code>Table A</code>. </li>
  </ul>
  
  <p>I am using Microsoft SQL Server.</p>
  
  <p>How would a SQL look like to join Tables A and B to get the desired result?
  Appreciate any of your ideas.</p>