title: Recursive Lag Column Calculation in SQL
body: <p>I am trying to write a procedure that inserts calculated table data into another table.</p>
  
  <p>The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not work!</p>
  
  <p>Such as:</p>
  
  <p>(Max is a function I created that returns the highest of two values)</p>
  
  <pre><code>Id   Product      Model      Column1    Column2
  1    A            1          5          =MAX(Column1*2, Lag(Column2))
  2    A            2          2          =MAX(Column1*2, Lag(Column2))
  3    B            1          3          =MAX(Column1*2, Lag(Column2))
  </code></pre>
  
  <p>If I try the above in SQL:</p>
  
  <pre><code>SELECT
      Column1, 
      MyMAX(Column1,LAG(Column2, 1, 0) OVER (PARTITION BY Product ORDER BY Model ASC) As Column2 
  FROM Source
  </code></pre>
  
  <p>...it says column2 is unknown.</p>
  
  <p>Output I get if I <code>LAG</code> the Column2 calculation:</p>
  
  <pre><code>Select Column1, MyMAX(Column1,LAG(Column1*2, 1, 0) OVER (PARTITION BY Product ORDER BY Model ASC) As Column2
  
  Id   Column1    Column2
  1    5          10
  2    2          10
  3    3          6
  </code></pre>
  
  <p>Why 6 on row 3? Because 3*2 > 2*2.</p>
  
  <p>Output that I want:</p>
  
  <pre><code>Id   Column1    Column2
  1    5          10
  2    2          10
  3    3          10
  </code></pre>
  
  <p>Why 10 on row 3? Because previous result of 10 > 3*2</p>
  
  <p>The problem is I can't lag the result of Column2 - I can only lag other columns or calculations of them!</p>
  
  <p>Is there a technique of achieving this with LAG or must I use Recursive CTE? I read that LAG succeeds CTE so I assumed it would be possible. If not, what would this 'CTE' look like?</p>
  
  <p>Edit: Or alternatively - what else could I do to resolve this calculation?</p>