title: Recursively retrieve LAG() value of previous record
body: <p>I've made the following calculation involving LAG():</p>
  
  <p><code>(lag(fValue,1,fValue) OVER (PARTITION BY Cluster ORDER BY iSequence) + fValue) / 2 as fValueAjusted</code></p>
  
  <p>It takes the previous (based on iSequence) record's fValue, sums with current one, and divides it by 2.</p>
  
  <p>But, instead of using fValue, I must do that using previous record's fValueAjusted.</p>
  
  <p>It means that first record's fValueAjusted will be its own fValue. Second record's fValueAjusted will be based on first record's fValue. And, starting from third record, it's calculation will be based on previous record's fValueAjusted.</p>
  
  <p>I need fValueAjusted to be calculated recursively over the previous record's fValueAjusted. I can't figure out how to do that.</p>
  
  <p>Update 1: This is an example of source data. Real table has hundreds of records and 80 clusters.</p>
  
  <pre><code>CREATE TABLE dbo.example (
      iUnity      int NOT NULL,
      Cluster     char(2) NOT NULL,
      fValue      float NOT NULL
  )
  
  15  A1      150
  17  A1      170
  21  B2      210
  23  B2      230
  71  C3      710
  </code></pre>
  
  <p>This is the CTE that calculates the sequence:</p>
  
  <pre><code>WITH cteSequencing AS (
      SELECT
          iUnity,Cluster
          ,fValue as fValueOriginal
          ,row_number() OVER (PARTITION BY Cluster ORDER BY fValueOriginal) as iSequence
      FROM dbo.example
  )
  </code></pre>
  
  <p>If fValueAjusted would be calculated based on fValueOriginal, the query would look like:</p>
  
  <pre><code>SELECT
      iUnity,Cluster,fValueOriginal
      ,(
          lag(fValue,1,fValue) OVER (PARTITION BY Cluster ORDER BY iSequence)
          + fValueOriginal
      ) / 2 as fValueAjusted
  FROM cteSequencing
  </code></pre>
  
  <p>But fValueAjusted of one record must be calculated based on fValueAjusted of previous record. It would be something like:</p>
  
  <pre><code>SELECT
      iUnity,Cluster,fValueOriginal
      ,(
          lag(fValueAjusted,1,fValueOriginal) OVER (PARTITION BY Cluster ORDER BY iSequence)
          + fValueOriginal
      ) / 2 as fValueAjusted
  FROM cteSequencing
  </code></pre>
  
  <p>Of course fValueAjusted isn't available when it executes. LAG() must go recursively, calculating the column for a record then providing this column for next record to use.</p>