;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
(
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.
Howdy!
ReplyDeleteYou 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
Good point Jeff... I can't wait for SQL Server to get enhanced analytic functions!
ReplyDelete