0

As it appears to be the fasted method, I am trying to use the "Update to a local variable" method to calculate running totals in SQL. To add an extra layer to this, I am interested in being able to group the running totals, but I can't make sense of where to make the tweaks to my query to do this (or if it is even possible with this method). Any and all help will be appreciated.

Below is the code that I used to create my query, and the article that led me to this method in the first place. How can I modify the query to have the running total reset for every daycount

http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl

Thanks

Reilly

user1551957
  • 33
  • 3
  • [Here is the answer to your question](http://stackoverflow.com/questions/10832067/partitioning-results-in-a-running-totals-query), but you should consider reading [this blog post by Aaron Bertrand as well](http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals). – Nikola Markovinović Oct 02 '12 at 22:40
  • @NikolaMarkovinović thanks so much for the response. I used a slight variation on what you linked to, and it is working fantastically. – user1551957 Oct 04 '12 at 22:56

1 Answers1

0

The links in the comments allowed me to come up with the right coding. Below is how the update function needs to be modified to group by Day in the above example.

UPDATE @salestbl
SET @RunningTotal = RunningTotal = sales +
    CASE WHEN daycount=@lastday THEN @RunningTotal ELSE 0 END
   ,@lastday=daycount
FROM @salestbl
user1551957
  • 33
  • 3