Interesting. I have been working on a similar task. This is to take away sales figures (already sold items) from a set of monthly sales forecast figures.

eg.

Sales Forecast for an item:

Month 1: 200, Sales 450 (already sold items in Month 1)

Month 2: 100 (no Sales beyond current month, only future orders)

Month 3: 100

Month 4: 120

I have to remove 450 from the month buckets, starting at M1.

So the update forecast would be:

M1: 0

M2: 0

M3: 0

M4: 70

There is an added constraint, which is to only only make adjustments up to a certain number of months in the future. eg.

If Months to Consider = 4 then the result would be as above.

But if Months to Consider = 3 then the result for Month 4 would remain at the original 120. The extra 70 from the Sales would become part of the original Sales forecast.

Orders (not shown) as opposed to Sales also affect the forecast.

Got no code to show, but it is similar to what you have shown.

But I ended up using a cursor around the procedure because I have 6,000 item forecasts to process. (I couldn't work out how to do the sub-selects without the cursor, and was running out of time).

I have not found anything much better than what you have shown. If I had some more time I would investigate, as I can't help feeling there may be some "Tally table" solution to this.