title: Using a running total calculated column in SQL Server table variable
body: <p>I have inherited a stored procedure that utilizes a table variable to store data, then updates each row with a running total calculation.  The order of the records in the table variable is very important, as we want the volume to be ordered highest to lowest (i.e. the running total will get increasingly larger as you go down the table).</p>
  
  <p>My problem is, during the step where the table variable is updated, the running total seems to be calculating , but not in a way that the data in the table variable was previously sorted by (descending by highest volume)</p>
  
  <pre><code>DECLARE @TableVariable TABLE ([ID], [Volume], [SortValue], [RunningTotal])
  
  --Populate table variable and order by the sort value...
  INSERT INTO @TableVariable (ID, Volume, SortValue)
     SELECT   
         [ID], [Volume], ABS([Volume]) as SortValue
     FROM  
         dbo.VolumeTable  
     ORDER BY 
         SortValue DESC
  
  --Set TotalVolume variable...
  SELECT@TotalVolume = ABS(sum([Volume])) 
  FROM @TableVariable
  
  --Calculate running total, update rows in table variable...I believe this is where problem occurs?
  SET @RunningTotal = 0
  
  UPDATE @TableVariable
  SET @RunningTotal = RunningTotal = @RunningTotal + [Volume]
  FROM @TableVariable
  
  --Output...
  SELECT  
      ID, Volume, SortValue, RunningTotal 
  FROM    
      @TableVariable      
  ORDER BY 
      SortValue DESC
  </code></pre>
  
  <p>The result is, the record that had the highest volume, that I would have expected the running total to calculate on first (thus running total = [volume]), somehow ends up much further down in the list.  The running total seems to calculate randomly</p>
  
  <p>Here is what I would expect to get:</p>
  
  <p><img src="https://i.stack.imgur.com/Bw0ko.jpg" alt="Good result"></p>
  
  <p>But here is what the code actually generates:</p>
  
  <p><img src="https://i.stack.imgur.com/NTvHy.jpg" alt="enter image description here"></p>
  
  <p>Not sure if there is a way to get the UPDATE statement to be enacted on the table variable in such a way that it is ordered by volume desc?  From what Ive read so far, it could be an issue with the sorting behavior of a table variable but not sure how to correct?  Can anyone help?</p>