Tuesday, April 27, 2010

Running total in SQL Server...

Not really sure how good this is... but seems to do the job!

;with theData (rowNum, GroupA, GroupRowNum, theValue) as
(
   select row_number() over (order by MajorGroup, GroupOrder),
   DataValue,
   row_number() over (partition by MajorGroup
      order by MajorGroup, GroupOrder),
   GroupOrder
   from DataTable
)
select X.GroupA, X.GroupRowNum, X.theValue +
   coalesce(
      (select sum(theValue)
      from theData Y
      where Y.GroupA=X.GroupA and Y.GroupRowNum > X.GroupRowNum), 0
   )
from theData X


For some reason I can't get an execution plan as I get an error - I've filed a Connect bug with Microsoft.

2 comments:

  1. Howdy!

    You might want to check the column order of the CTE with the SELECT list of that same CTE. ;-)

    You also might want to read the article I wrote on Triangular Joins on SQLServerCentral.com to see why that particular method for doing running totals is usually not a good idea.

    http://www.sqlservercentral.com/articles/T-SQL/61539/

    --Jeff Moden

    ReplyDelete
  2. Good point Jeff... I can't wait for SQL Server to get enhanced analytic functions!

    ReplyDelete