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.