Please
I've migrated my Access 2002 VBA program over to SQL 2000. In ACCESS I had a query that creating running totals in a table. It worked but took hours to run with a table of over 35000 rows and now I want to convert that to a stored procedure. I am in need of how this should be designed or if a reference article exists for this
Here is a sample of what is needed

THIS TABLE CONTAINS "monthly revenue". I NEED TO FILL IN THE "quarterly revenue" by summing prior 3 months
ACCT month monthly revenue quarterlyrevenu
10 01-01-03 100.00 100.0
10 01-01-03 200.00 300.0
10 01-01-03 300.00 600.0
10 01-01-03 400.00 900.00
10 01-01-03 500.00 1200.0

How can this be done? Please help. Mahalo in Advance

Your sample data shows the same month though. In any case do:

SELECT t1.acct, t1.month, t1.revenue,
( SELECT SUM( t2.revenue )
FROM tbl t2
WHERE t1.acct = t2.acct
AND t1.month >= t2.month ) AS "cumulative"
FROM tbl t1 ;

--
Anith

Bret,

I think you are asking something different from what others thought
you were. If you want every month shown with its revenue and also the
revenue for he quarter ending that month, I think this will do it. The
table variable is just to make this an easier cut-and-paste repro to run
in Query Analyzer:

declare @revenue table (
Acct int not null,
Month datetime,
Revenue money
)
insert into @revenue
select
EmployeeID,
dateadd(month,ProductID,'19990101'),
EmployeeID*ProductID + rand(binary_checksum(newid()))
from Northwind..Products, Northwind..Employees
where ProductID between 50 and 60
and EmployeeID between 4 and 6

select
Acct,
min(Month) as QuarterEnds,
sum(Revenue0) as MonthlyRevenue,
sum(Revenue) as QuarterRevenue
from (
select
Acct,
dateadd(month,M,Month) as Month,
case when M = 0 then Revenue end as Revenue0,
Revenue
from @revenue R
cross join (
select 0 as M union all select 1 union all select 2
) Three
) T
group by
Acct, Month
order by
Acct,
QuarterEnds

-- Steve Kass
-- Drew University
-- Ref: 04C71303-10A4-4CE5-A4CA-2A293594D96B

[quoted text, click to view]