Time Interval Summing

• Suri-154872

  SSC Enthusiast

  Points: 181

  More actions

  December 6, 2005 at 10:26 am

  #102973

  I have in the table a [time] and callsoffered fields. The callsOffered is logged into this table in 15min interval.

  For ex: 00:00(time) 100(callsOffered), 00:15 200, 00:30 210, 1:00 250. The data that I want to return is by 30min interval.

  Is there any way I can accomplish this. Any help will be appreciated.

  Thanks

  Suresh

• Steve Angus

  SSC Rookie

  Points: 47

  More actions

  December 6, 2005 at 11:20 am

  #608007

  Suresh,

  It's not particularly pretty but something along these lines might work :

  SELECT datepart(hh,[time]), case when datepart(mi,[time]) < 30 then 1 else 2 end, sum([callsoffered])

  FROM <table>

  GROUP BY datepart(hh,[time]), case when datepart(mi,[time]) < 30 then 1 else 2 end

  ORDER BY datepart(hh,[time]), case when datepart(mi,[time]) < 30 then 1 else 2 end

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  December 6, 2005 at 1:20 pm

  #608036

  set nocount on

  declare @myData table (mytime datetime, Calls int)

  insert @myData

  select '12/01/2005 00:00', 100 UNION

  select '12/01/2005 00:15', 200 UNION

  select '12/01/2005 00:30', 210 UNION

  select '12/01/2005 00:45', 250 UNION

  select '12/01/2005 01:00', 300 UNION

  select '12/01/2005 01:15', 440 UNION

  select '12/01/2005 01:30', 534 UNION

  select '12/01/2005 01:45', 900

  select main.*

  from

   @myData main

  join(

     select rank=count(*), a1.mytime

     from @myData a1, @myData a2

     where a1.mytime >= a2.mytime

     group by a1.mytime

      ) pre

  on

   pre.mytime = main.mytime

  and pre.rank % 2 = 1

  Regards,
  gova

• Suri-154872

  SSC Enthusiast

  Points: 181

  More actions

  December 6, 2005 at 2:01 pm

  #608048

  Thanks a lot. I think you all gave me some valuable inputs and I am going to put it to implement..