How to calculate the average of the first rows of each hour-CodePudding

We have a table with values, every five minutes a new row appears in the table with a numerical value and time, how to calculate the average value for the first 45 minutes of each hour.

I have a query that calculates the average value for each hour

SELECT hh=CONVERT(CHAR(13),[DateMeasure],126),
[Fe_Ka]=AVG([Fe_ka]) FROM Results1 
GROUP BY CONVERT(CHAR(13),[DateMeasure],126) 
ORDER BY hh;

but I have no idea how to make a selection of 45 minutes at the beginning of each hour

Initial table

Desired Format

The initial table includes three columns: Measurement date(datetime), measurement id(int), value(float). A new line is added every five minutes. Need a table with the time by hour and the average value for the first 45 minutes of this hour

CodePudding user response：

Use DATEPART() to get the minute and CASE expression and to check for the time is within HH:45 mintues

select  TheDate = convert(date, DateMeasure), 
        TheHour = datepart(hour, DateMeasure),
        TheAvg  = avg(case when datepart(minute, DateMeasure) <= 45 then TheVal end)
from    TheTable
group by convert(date, DateMeasure), datepart(hour, DateMeasure)