how to calculate the avgerage of the value based on time interval

• jprabha.d

  Ten Centuries

  Points: 1086

  More actions

  December 4, 2009 at 10:34 pm

  #143286

  HI All,

  I have a table "A" which contains 2 columns in it

  1.Datetime 2.Memory_Available_Mbyte

  this table has the data as shown below

  DateTime Memory_Available_Bytes

  11/01/2009 00 1556

  11/01/2009 01 1545

  11/01/2009 02 2330

  11/01/2009 03 1876

  11/01/2009 04 1456

  '

  '

  '

  '

  and so on this goes on getting a data on hourly bases till date say 11/30/2009.

  i need to get the Average of Memory_Available_Mbytes by time interval of every 6 hours ,how to write a sql query

  for this ,to obtain the best result.

  thanks in advanced:-)

  jaya

• Dugi

  SSCoach

  Points: 17998

  More actions

  December 5, 2009 at 12:53 am

  #1088126

  More explanation in second column what data type it is, or can you post the structure of you table with these two columns ...

  ============================================================
  SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
  http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

• arun.sas

  SSChampion

  Points: 11831

  More actions

  December 5, 2009 at 1:08 am

  #1088128

  Hi, try this

  create table #temp2

  (DATE1 smalldatetime,memory float)

  insert into #temp2

  select '2009-01-01 01:00:00',11

  union all

  select '2009-01-01 02:00:00',22

  union all

  select '2009-01-01 08:00:00',33

  union all

  select '2009-01-01 10:00:00',44

  union all

  select '2009-01-01 15:00:00',55

  union all

  select '2009-01-02 06:00:00',66

  union all

  select '2009-01-02 12:00:00',77

  union all

  select '2009-01-02 18:00:00',88

  union all

  select '2009-01-02 23:00:00',99

  create table #day_seg

  (seg_ment int,fromtime int,totime int)

  insert into #day_seg

  select 1,0,6

  union all

  select 2,7,12

  union all

  select 3,13,18

  union all

  select 4,19,23

  select * from #day_seg

  select * from #temp2

  select DATEADD(day,DATEDIFF(day, 0, a.DATE1),0)[DATE],b.seg_ment[RANGE],avg(memory)[AVERAGE]

  from #temp2 a

  inner join

  #day_seg b

  on

  datepart(hh,a.DATE1) between b.fromtime and b.totime

  group by DATEADD(day,DATEDIFF(day, 0, a.DATE1),0),b.seg_ment

  order by DATEADD(day,DATEDIFF(day, 0, a.DATE1),0),b.seg_ment

• jprabha.d

  Ten Centuries

  Points: 1086

  More actions

  December 5, 2009 at 2:07 am

  #1088135

  Hi ,

  the first column is of DateTime

  The second column is of varchar datatypes.

  thanx

  Jaya

• arun.sas

  SSChampion

  Points: 11831

  More actions

  December 5, 2009 at 2:50 am

  #1088138

  jprabha.d (12/5/2009)

  ---

  Hi ,

  the first column is of DateTime

  The second column is of varchar datatypes.

  thanx

  Jaya

  Hi,

  create table #temp2

  (DATE1 smalldatetime,memory varchar(5))

  insert into #temp2

  select '2009-01-01 01:00:00',11

  union all

  select '2009-01-01 02:00:00',22

  union all

  select '2009-01-01 08:00:00',33

  union all

  select '2009-01-01 10:00:00',44

  union all

  select '2009-01-01 15:00:00',55

  union all

  select '2009-01-02 06:00:00',66

  union all

  select '2009-01-02 12:00:00',77

  union all

  select '2009-01-02 18:00:00',88

  union all

  select '2009-01-02 23:00:00',99

  create table #day_seg

  (seg_ment int,fromtime int,totime int)

  insert into #day_seg

  select 1,0,6

  union all

  select 2,7,12

  union all

  select 3,13,18

  union all

  select 4,19,23

  select * from #day_seg

  select * from #temp2

  select DATEADD(day,DATEDIFF(day, 0, a.DATE1),0)[DATE],b.seg_ment[RANGE],avg(cast(memory as float))[AVERAGE]

  from #temp2 a

  inner join

  #day_seg b

  on

  datepart(hh,a.DATE1) between b.fromtime and b.totime

  group by DATEADD(day,DATEDIFF(day, 0, a.DATE1),0),b.seg_ment

  order by DATEADD(day,DATEDIFF(day, 0, a.DATE1),0),b.seg_ment