Building an aggregate View with cumulative totals

[synapsevampire]

I have a View (pasted below) which creates sums on various groupings, in particular I'd like to focus your attention on the work week (weekofqtr).

What I would like to do is add in another column which performs a cumulative running total based on the weeks of the quarter for the summed fields shown in the query, respecting the other groups as well of course.

So the current returns:

weekofqtr detailtotal
1 100
2 300
3 600

The new cumulative column would return:

weekofqtr detailtotal Cumul_amount
1 100 100
2 300 400
3 600 1000

I would also like to keep this as a View (possibly a table, see below) instead of going to a Stored Procedure.

My first thought was to brute force it by using a subquery that states

select sum(detailtotal) from myview A where <fields = fields> and A.weekofqtr <= myview.weekofqtr

This would likely prove slow though, but I'm also open to the notion of using SQL to generate a table which will be created daily or some such to do this.

All thoughts welcomed.

SELECT dbo.data.qtr, dbo.data.geoTJ, dbo.data.regionTJ, dbo.data.subregionTJ, dbo.data.directrep, dbo.data.partnerrep, dbo.CalendarWeek.weekofqtr,
dbo.data.channel, dbo.data.orderid, dbo.data.orderdate, dbo.data.class, dbo.data.product, dbo.data.reseller, dbo.data.billtoorganization,
dbo.data.territoryTJ, dbo.data.alliance, dbo.data.LastRunDate, SUM(dbo.data.quantity) AS Qty, SUM(dbo.data.detailtotal) AS BookingsTotal
FROM dbo.data LEFT OUTER JOIN
dbo.CalendarWeek ON dbo.data.qtr = dbo.CalendarWeek.qtr AND dbo.data.week = dbo.CalendarWeek.weekid
WHERE (NOT (dbo.data.chanreg = N'Excluded'))
GROUP BY dbo.data.qtr, dbo.data.geoTJ, dbo.data.regionTJ, dbo.data.subregionTJ, dbo.data.directrep, dbo.CalendarWeek.weekofqtr, dbo.data.partnerrep,
dbo.data.channel, dbo.data.orderid, dbo.data.orderdate, dbo.data.class, dbo.data.product, dbo.data.reseller, dbo.data.billtoorganization,
dbo.data.territoryTJ, dbo.data.alliance, dbo.data.LastRunDate

-k

 

[mrdenny]

Directly via a view this will be very hard to do. You could add the WITH ROLLUP option to the select statment, and that will give you subtotals and totals.

Do pull this off you'd need a temporary table and a function. The function can be used within the view, but the temporary table can't be created within the view.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[synapsevampire]

We'd figured out how to do it within a View, it was just Ungawduslessly slow.

Do you know of any examples that create cumulative running totals using code?

-k

 

[SQLSister]

personally I would create a running total table and have a trigger update it anytime the data changes. Running totals can be very slow if there is a lot of data.

Questions about posting. See faq183-874