Return average in 15 minute intervals

• owilson

  SSC Enthusiast

  Points: 181

  More actions

  July 7, 2008 at 1:15 pm

  #193523

  I have a table that contains some network throughput data that is collected every 9 minutes. I need to average this data in 15 minute intervals between the hours of 6:00 am and 12:00pm (inclusive) for the past two weeks, but only weekdays.

  I need one average for each 15 minute period that includes all weekdays.

  sample table data:

  DateTimeIn_AveragebpsOut_Averagebps

  6/16/08 6:01 AM135151.9141747.9

  6/16/08 6:10 AM188344.1144173.6

  6/16/08 6:19 AM257807.4147395.2

  6/16/08 6:28 AM253137.6178196

  6/16/08 6:37 AM259033.4204211.6

  6/16/08 6:46 AM221271.3 180262.5

  6/16/08 6:55 AM494910.3763287.9

  6/16/08 7:04 AM275931.6234089.9

  6/16/08 7:13 AM374923.41174096

  6/16/08 7:22 AM382675.1331110.1

  6/16/08 7:32 AM475170.6563870.4

  Can anyone provide some assistance please? Thanks in advance.

• noeld

  SSC Guru

  Points: 96590

  More actions

  July 7, 2008 at 1:39 pm

  #839143

  Maybe something like this :

  DECLARE @T TABLE ( dt datetime, In_Averagebps decimal (9,2),Out_Averagebps decimal (9,2) )

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 6:01 AM',135151.9,141747.9)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 6:10 AM',188344.1,144173.6)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 6:19 AM',257807.4,147395.2)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 6:28 AM',253137.6,178196)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 6:37 AM',259033.4,204211.6)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 6:46 AM',221271.3,180262.5)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 6:55 AM',494910.3,763287.9)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 7:04 AM',275931.6,234089.9)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 7:13 AM',374923.4,1174096)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 7:22 AM',382675.1,331110.1)

  INSERT INTO @T (dt,In_Averagebps,Out_Averagebps ) VALUES ('6/16/08 7:32 AM',475170.6,563870.4)

  SELECT slice, AVG(In_Averagebps) In_avg , AVG (Out_Averagebps) Out_avg

  FROM

  (

  SELECT DATEADD( mi, DATEPART(mi, dt) / 15 * 15, DATEADD(mi, -DATEPART(mi, dt), dt)) slice , *

  FROM @T

  ) AS data

  GROUP BY slice

  ORDER BY slice

  
  * Noel