calculate the average value in a certain time interval

Question

I have a table in which values are written every 30 minutes I want to calculate the average value over a certain time period over several days.

CREATE TABLE [dbo].[#TABLE_1](
[numreq] [int] NULL,
[rangetime] [nvarchar](300) NULL
) ON [PRIMARY]

insert into [dbo].[#TABLE_1] values('2','2019-03-10 12:00:00.000')
insert into [dbo].[#TABLE_1] values('3','2019-03-10 12:30:00.000')
insert into [dbo].[#TABLE_1] values('4','2019-03-10 13:00:00.000')
insert into [dbo].[#TABLE_1] values('5','2019-03-10 13:30:00.000')
insert into [dbo].[#TABLE_1] values('6','2019-03-11 12:00:00.000')
insert into [dbo].[#TABLE_1] values('7','2019-03-11 12:30:00.000')
insert into [dbo].[#TABLE_1] values('8','2019-03-11 13:00:00.000')
insert into [dbo].[#TABLE_1] values('9','2019-03-11 13:30:00.000')

I wrote a sql query, but it does not give the desired result

select avg([numreq]),[rangetime]
from [dbo].[#TABLE_1]
group by [rangetime]

The result I want to get:

numreq rangetime
4  12:00
5  12:30
6  13:00
7  13:30

SELECT and GROUP BY the time portion of your date time field. See the flagged duplicate for how to do this. — Tab Alleman, Mar 11 '19 at 12:54

score 1 · Answer 1 · answered Mar 11 '19 at 12:53

You can use cast() to parse only time :

select avg(numreq), cast(rangetime as time) as rangetime
from [#TABLE_1]  t
group by cast(rangetime as time);

If the rangetime has not desired type then you can use substring():

substring(rangetime, 12, len(rangetime))

calculate the average value in a certain time interval

1 Answers1