title: How to fill in a missing value from previous rows
body: <p>Consider this table (test):</p>
  
  <pre><code>C1  C2  Data
  ----------------
  1   101 A1
  1   104 A4
  1   105 A5
  2   101 B1
  2   102 B2
  2   103 B3
  2   105 B5
  </code></pre>
  
  <p>and this table (OtherTable)</p>
  
  <pre><code>C2
  ---
  100
  101
  102
  103
  104
  105
  106
  </code></pre>
  
  <p>What I want is for the missing rows above to be filled in from the last available row and from the OtherTable
    - ie. C1 gets same value, C2 comes from OtherTable and Data gets same value.</p>
  
  <pre><code>C1  C2  Data
  ----------------
  1   101 A1
  1   102 A1   -- &lt;&lt;&lt; Filled in from OtherTable &amp; last available row 'A1'
  1   103 A1   -- &lt;&lt;&lt; Filled in from OtherTable &amp; last available row 'A1'
  1   104 A4
  1   105 A5
  2   101 B1
  2   102 B2
  2   103 B3
  2   104 B3  -- &lt;&lt;&lt; Filled in from OtherTable &amp;  last available row 'B3'
  2   105 B5
  </code></pre>
  
  <p>I've built this:</p>
  
  <pre><code>WITH Keys AS 
  (
      SELECT 
          T.C1, 
          O.C2
      FROM
          TEST T
          CROSS APPLY (SELECT C2 FROM OtherTable) O 
      GROUP BY
          T.C1, 
          O.C2
  ),
  MaxMin AS
  (
      SELECT
          C1,
          MIN(C2) LowerBound,
          MAX(C2) UpperBound
      FROM
          Test 
      GROUP BY
          C1
  )
  SELECT
      K.C1, 
      K.C2,
      T.Data
  FROM
      Keys K
      LEFT JOIN Test T 
          ON      
              T.C1 = K.C1 
              AND T.C2 = K.C2 
      INNER JOIN MaxMin M 
          ON
              K.C1 = M.C1 
              AND K.C2 BETWEEN M.LowerBound AND M.UpperBound
  </code></pre>
  
  <p>I get this far</p>
  
  <pre><code>C1  C2  Data
  ----------------
  1   101 A1
  1   102 NULL
  1   103 NULL
  1   104 A4
  1   105 A5
  2   101 B1
  2   102 B2
  2   103 B3
  2   104 NULL
  2   105 B5
  </code></pre>
  
  <p>But I can't see how to apply Data column, and I also feel a recursive CTE is in order reduce the above query but I cannot see how.</p>
  
  <hr>
  
  <p>(Here is TSQL to get the sample up)</p>
  
  <pre><code>IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Test') 
      DROP TABLE Test;
  
  CREATE TABLE Test (C1 INT, C2 INT, Data NVARCHAR(10));
  
  INSERT INTO Test (C1, C2, Data) VALUES
  (1, 101, 'A1'),
  --(1, 102, 'A1'),
  --(1, 103, 'A1'),
  (1, 104, 'A4'),
  (1, 105, 'A5'),
  (2, 101, 'B1'),
  (2, 102, 'B2'),
  (2, 103, 'B3'),
  --(2, 104, 'B3'),
  (2, 105, 'B5');
  
  
  IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'OtherTable') 
      DROP TABLE OtherTable;
  
  CREATE TABLE OtherTable (C2 INT);
  
  INSERT INTO OtherTable (C2) VALUES
  (100),
  (101),
  (102),
  (103),
  (104),
  (105),
  (106);
  </code></pre>