Calculating a Moving Average

One of my tables contains meteorological measurements per minute (e.g., temperature and humidity), and I'm trying to use a GROUP BY clause to calculate a moving daily average per month for these measurements. I want to express this average in a smalldatetime data type. Calculating a daily average temperature is easy, but I want to union these averages for a month. How can I create the data I need?

To see how you can return the moving average, first create a Measurements table and populate it with sample data for testing. Listing 1 populates the Measurements table with one year's worth of six random daily temperature measurements. The trick in calculating a daily temperature average is to use an expression in the GROUP BY clause. The expression CONVERT(char(10), DT, 112) in Listing 2's script extracts only the date part of the DT column, which contains the measurement's date and time. By using this expression in the GROUP BY clause, you return a separate row for each day. Then, you need to request the average temperature and filter the desired month, as Listing 2 shows. This statement returns a month's worth of daily temperature averages, which constitute the moving average. Table 1 displays the output from Listing 2.