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),
   row_number() over (partition by MajorGroup
      order by MajorGroup, GroupOrder),
   from DataTable
select X.GroupA, X.GroupRowNum, X.theValue +
      (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.