Calculating 1/2 hour averages

[Simple Man]

Looking for some ideas on how to average my data by 1/2 increments.

We've got a weather station and the data is written to a sql database every 2 minutes. I need to someone average the data by 1/2 hour increments. So, data between 3pm and 3:30pm need to be averaged for the 3:30pm time. 3:30pm to 4pm needs to be avg for the 4pm reading, etc.

The 1/2 readings will be for a data export that I will provide based on date criteria.

Anyone have an idea on how I should approach this? Not sure if I should do this using SQL or just code it in vb.net and then export it.

Doing this hourly would be a lot simpler because I could group it by the hour....but having the do this for 1/2 hours throws a little wrinkle in it.

Thoughts?

Thanks.

Nick

 

[ss7thirty]

You will want to use a SQL Aggregate function AVG and group you data based on the time field.

For the first half hour

select AVG(TheFieldYouWantToAVG),Year(snapshotdate), Month(snapshotdate), Day(snapshotdate),  Datepart(Hour,Snapshotdate), Datepart(Minute, snapshotdate)
from YourTable 
GROUP BY Year(snapshotdate), Month(snapshotdate), Day(snapshotdate), Datepart(Hour,Snapshotdate), Datepart(Minute, snapshotdate)
HAVING Datepart(Minute, snapshotdate) <= 30

For the second half hour

select AVG(TheFieldYouWantToAVG),Year(snapshotdate), Month(snapshotdate), Day(snapshotdate),  Datepart(Hour,Snapshotdate), Datepart(Minute, snapshotdate)
from YourTable 
GROUP BY Year(snapshotdate), Month(snapshotdate), Day(snapshotdate), Datepart(Hour,Snapshotdate), Datepart(Minute, snapshotdate)
HAVING Datepart(Minute, snapshotdate) > 30

 

[Simple Man]

That makes sense. I'll give that a shot and see what I can come up with. I'll let you know.

Thanks.

Nick

 

[Simple Man]

Perfect...thanks.