title: Fill gaps with set based on priority
body: <p><strong>Goal</strong></p>
  <p>For each <code>Foo</code> we should try the fill the time frames as much as possible with records from <code>FooBar</code>. It's ok to have empty time frames when no records exists in <code>FooBar</code>.
  The records in <code>FooBar</code> behave as a set.
  Meaning if <code>FooId</code> and the time frame are (exactly) the same the BarId's are all valid in this time frame.
  Gaps should be filled based on the sets priority.</p>
  <p><strong>Table structure</strong></p>
  <pre><code>CREATE TABLE Foo(
      FooId INT NOT NULL,
      ValidFrom DATETIME NOT NULL,
      ValidUntil DATETIME NOT NULL,
  )
  
  CREATE TABLE FooBar (
      FooId INT NOT NULL,
      BarId INT NOT NULL,
      ValidFrom DATETIME NOT NULL,
      ValidUntil DATETIME NOT NULL,
      Priority TINYINT NOT NULL,
  )
  </code></pre>
  <p><strong>Sample data</strong></p>
  <pre><code>INSERT INTO Foo(FooId, ValidFrom, ValidUntil)
  VALUES 
    (1, '2020-01-01', '2021-12-31')
  , (2, '2020-01-01', '2021-06-30')
  
  INSERT INTO FooBar(FooId, BarId, ValidFrom, ValidUntil, Priority)
  VALUES 
  -- First set for FooId = 1 with prio 1
    (1, 1, '2021-01-01', '2021-03-01', 1)
  , (1, 2, '2021-01-01', '2021-03-01', 1)
  , (1, 3, '2021-01-01', '2021-03-01', 1)
  -- Second set for FooId = 1 with prio 2 
  , (1, 1, '2021-02-01', '2021-06-01', 2)
  , (1, 2, '2021-02-01', '2021-06-01', 2)
  -- Third set for FooId = 1 with prio 3
  , (1, 1, '2021-01-01', '2021-12-31', 3)
  , (1, 2, '2021-01-01', '2021-12-31', 3)
  , (1, 3, '2021-01-01', '2021-12-31', 3)
  -- Fourth set for FooId = 1 with Prio 1
  , (1, 4, '2021-04-01', '2021-04-02', 1)
  , (1, 5, '2021-04-01', '2021-04-02', 1)
  -- First set for FooId = 2 with prio 3
  , (2, 6, '2021-01-01', '2021-04-02', 3)
  
  </code></pre>
  <p><strong>Expected result</strong>
  <em>Origin column to clarify, shouldn't be part of the generated result set</em></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>FooId</th>
  <th>BarId</th>
  <th>ValidFrom</th>
  <th>ValidUntil</th>
  <th>Origin</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>2021-01-01</td>
  <td>2021-03-01</td>
  <td>First set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>2021-01-01</td>
  <td>2021-03-01</td>
  <td>First set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>3</td>
  <td>2021-01-01</td>
  <td>2021-03-01</td>
  <td>First set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>2021-03-02</td>
  <td>2021-03-31</td>
  <td>Second set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>2021-03-02</td>
  <td>2021-03-31</td>
  <td>Second set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>4</td>
  <td>2021-04-01</td>
  <td>2021-04-02</td>
  <td>Fourth set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>5</td>
  <td>2021-04-01</td>
  <td>2021-04-02</td>
  <td>Fourth set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>2021-04-03</td>
  <td>2021-06-01</td>
  <td>Second set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>2021-04-03</td>
  <td>2021-06-01</td>
  <td>Second set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>2021-06-02</td>
  <td>2021-12-31</td>
  <td>Third set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>2021-06-02</td>
  <td>2021-12-31</td>
  <td>Third set</td>
  </tr>
  <tr>
  <td>1</td>
  <td>3</td>
  <td>2021-06-02</td>
  <td>2021-12-31</td>
  <td>Third set</td>
  </tr>
  <tr>
  <td>2</td>
  <td>6</td>
  <td>2021-01-01</td>
  <td>2021-12-31</td>
  <td>First set (FooId = 2)</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I know this is possible with a cursor or while loop, but I'm looking for a more performant/elegant solution.</p>
  <p>Compatibility level is: 130</p>