You can use a powerful but little-known technique, namely a running update with variables:
DECLARE @Sales DECIMAL(10,2), @MV DECIMAL(10,2)
SELECT @Sales = 0, @MV = 0
UPDATE Table1
SET @MV = MV = CASE WHEN nDay = 1 THEN Sales ELSE 0.8*@MV + 0.2*Sales END,
@Sales = Sales
You do need a clustered index on the field that determines the order (nDay in this case) to ensure that the rows are updated in the right order. This method is MUCH faster than using a cursor or a while loop!
Jorg Jansen
Manager Database Development
Infostradasports.com
Nieuwegein
The Netherlands
Jorg Jansen
Manager Database Development
Infostradasports.com
Nieuwegein
The Netherlands