Update Help 1

[ehenry]

I Have a table that contains Temperature data For a list of 230 Stations The table has a Station ID, a Date column, and an Avg Temp column with data in it. I have added another column to hold the 7 day trailing average, which is simply the average of the past seven days.

Each day when new data comes in I want to update That 7 day trialing avg column with the average. So far I have a select statement to gather the trailing averages for today for each station. Now I need to append the average to the matching station ID and Date.

Here is my code so far:

SELECT [Station ID], GetDate() as Dates, ROUND(AVG([Avg Temp]),1) FROM Actuals
WHERE Dates Between DATEADD(DAY,-7,GETDATE()) AND GETDATE()
GROUP BY [Station ID]

Any Help would be appreciated.

 

[markros]

SQL Server 2005 and up:

; with cte_AVG as (SELECT [Station ID], DateColumn, ROUND(AVG([Avg Temp]),1) OVER(partition by [Station ID]) as AvgTemp FROM Actuals WHERE DateColumn Between DATEADD(DAY,-7,GETDATE()) AND GETDATE()GROUP BY [Station ID])

Update A Set Avg_Temp = C.AvgTemp from Actuals A inner join cte_AVG C on A.[Station ID] = C.[Station ID] and A.DateColumn = C.DateColumn

 

[markros]

Sorry, in the CTE there should be no GROUP BY clause.

 

[ehenry]

Thank You I will try this.

 

[ehenry]

This is the right idea of what I need, but it is placing the cte_AVG Value into rows on the Actuals table with Dates between GetDate-7 and Getdate() rather than just on the row with the current date. If you need me to clarify further please let me know. Thanks for your help.

 

[jbenson001]

show some sample data and desired results

 

[gmmastros]

Does this return the correct data?

Select Top 100 A.StationId, A.Dates, Avg(B.AvgTemp) As Trailing7DayAvg
From   YourTableNameHere A
       Inner Join YourTableNameHere B
         On A.StationId = B.StationId
         And B.Dates Between A.Dates -6 And A.Dates
Group By A.StationId, A.Dates
Order By A.Dates DESC

Note that I put a TOP 100 in the query and also an order by. This will limit the results you get to the the first 100 rows sorted descending by dates. You may need to slightly alter the column names. If this returns the correct data, and you need further help in converting this to an update query, let me know.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I see, you want only the latest date, right? If so, then

; with cte_AVG as select * from (SELECT [Station ID], DateColumn, ROUND(AVG([Avg Temp]),1) OVER(partition by [Station ID]) as AvgTemp, row_Number() over (partition by [Station ID] order by DateColumn Desc) as rn FROM Actuals WHERE DateColumn Between DATEADD(DAY,-7,GETDATE()) AND GETDATE()) X where rn = 1)

Update A Set Avg_Temp = C.AvgTemp from Actuals A inner join cte_AVG C on A.[Station ID] = C.[Station ID] and A.DateColumn = C.DateColumn

If you have a PK in the Actuals table (I hope you do), you can use it instead of Station ID/DateColumn combination to link CTE with Actuals.

 

[ehenry]

Thank you for the help this worked perfect. For some reason its not letting me use the round function in this context, but no big deal. Thanks again.