Hi All, I have this table: DAILY_METRICS(Date, Charges, AverageDailyCharges). I insert the Date (daily for 1 year) and Charges (sum of all charges for that day) columns. Now, I am interested in filling the AverageDailyCharges = Last 30 days charges / 30. I don't know if this is possible or if it even makes sense here. I have tired many different Update statements with no luck. Thanks!Linda

Off the top of my head (so probably some sytax errors!); UPDATE DAILY_METRICS SET AverageDailyCharges = da.AverageDailyCharges FROM DAILY_METRICS dm INNER JOIN (SELECT MAX(Date) Date, AVERAGE(Charges) AverageDailyCharges FROM DAILY_METRICS WHERE Date BETWEEN DATEADD(dd,-29,GETDATE()) AND GETDATE()) da ON dm.Date = da.Date This will update every date, including the first 29 days, even though the data is incomplete. If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.

I have not had any luck with this code. I have tried! It looks so promising. I will keep trying but are there any other suggestions? Thanks!Linda

If you only want to update last 30 days, then try: declare @AvgCharges decimal(10,2) select @AvgCharges = sum(Charges)/30.0 from Daily_Metrics Where [Date] between dateadd(day,-30, [Date]) and [Date]) UPDATE Daily_Metrics SET AverageDailyCharges = @AvgCharges where [Date] between dateadd(day,-30, [Date]) and [Date]) For every expert, there is an equal and opposite expert. - Becker's Law My blog

Thanks to both of you. I ended up this this: declare @System_Id varchar(2) declare @Location_Cd varchar(2) declare @Create_Date datetime declare @AvgDailyNetRevenue decimal(10,2) declare AverageDailyNetRevenue insensitive cursor for select System_Id, Location_Cd, Create_Date from BAM_Daily_Metrics open AverageDailyNetRevenue fetch next from AverageDailyNetRevenue into @System_Id, @Location_Cd, @Create_Date WHILE( @@fetch_status <> -1 ) BEGIN SELECT @AvgDailyNetRevenue = AVG(Charges)/30.0 FROM Bam_Daily_Metrics WHERE Create_Date > DATEADD(day,-30,@Create_Date) AND Create_Date < = @Create_Date AND System_Id = @System_Id AND Location_Cd = @Location_Cd UPDATE BAM_DAILY_METRICS SET Avg_Daily_Net_Revenue = @AvgDailyNetRevenue WHERE System_Id = @System_Id AND Location_Cd = @Location_Cd AND Create_Date = @Create_Date fetch next from AverageDailyNetRevenue into @System_Id, @Location_Cd, @Create_Date END Linda

If you needed to update the Average value for every 30 days, then I was thinking about a CROSS APPLY solution when I was making my suggestion. You may try it as an alternative to the cursor based solution and compare the speed.For every expert, there is an equal and opposite expert. - Becker's Law My blog