title: SQL Server 2012 - Using LAG to get data off previous rows
body: <p>I've been able to use LAG to get data from a previous row but I'm struggling to use it to resolve a more complicated problem.</p>
  
  <p>Essentially for each episode in my table there might be, say, 10 unique attendances.  Each attendance has a value of 1, 2 or 3.</p>
  
  <p>What I need to accomplish is when an attendance value increases all of the following rows must have the same value.</p>
  
  <p>e.g.</p>
  
  <pre><code>Episode Attendance Value  New Value
   12345      1        1        1
   12345      2        1        1
   12345      3        2        2
   12345      4        1        2
   12345      5        1        2
   12345      6        2        2
   12345      7        3        3
   12345      8        1        3
   12345      9        1        3
   12345      10       1        3
   34567      1        1        1
   34567      2        2        2
   34567      3        1        2
   34567      4        2        2
   34567      5        1        2
   34567      6        3        3
   34567      7        1        3
   56789      1        2        2
   56789      2        1        2
   56789      3        1        2
  </code></pre>
  
  <p>So the new value can increase from a 1 to a 2 or 3 and from a 2 to a 3, but it shouldn't ever decrease.</p>
  
  <p>Any help with this would be greatly appreciated.  Using LAG might not be the right answer but I'm a bit of a novice as you might have gathered.</p>
  
  <p>Here's the SQL I've got so far:</p>
  
  <pre><code>    SELECT *,
  
  
  NewValue = 
  CASE    WHEN EPISODEID = Lag(EpisodeID,1) OVER(ORDER BY EpisodeID Asc) AND
   LAG(Value, 1) OVER(ORDER BY [EpisodeID] asc, Attendance) &gt;= Value THEN LAG(Value,1)
   OVER(ORDER BY [EpisodeID] asc, Attendance)
  
          ELSE        Value
          END 
  FROM TABLE
  </code></pre>
  
  <p>It only works for one row and I can see why, but I haven't found enough guidance to improve on it.</p>
  
  <p>Frustratingly, I could do this in Excel but I'm trying to improve my SQL skills</p>