Jeff, thanks for this article I learn something new.

I was relying on Reporting Service reports, cubes and MDX to do the running total for me. This article shows me a way to do it in classic reports.

I wonder if the same type of technique could be used as a general way to get a rid of all cursors and while loops. I stay away from but sometimes have no alternative.

I have a specific problem now. I must create a table to report on contract compliance. I can have multiple contracts for the same customer with or without the same products in them. I can have different dates of start and finish. Dates can overlap. The revenue table does not have any contract info.

To merge contract info with revenue data, I must use while loops and for each row, I put the contract num, the customer, the product, the start and finish date into variables, then in a while loop I add row by row the combined data in the result table by merging the Contract num with the matching data found in the revenue table.

When done one can look at performance on a contract by contract basis. The sum of all contracts for a given customer product and period could double count revenue. This is understood and acceptable.

I have 11 different type of contracts, the level of grouping changes and the nature of the revenue changes as well, can be direct, indirect and has to look at both revenue and orders. Takes 5 hours to run on a 64 bits machine, once a week on Saturday.

These tables have million of rows, it is working but if it could work in a fraction of the time it would be better.