Frequency of inserts

• _simon_

  SSCrazy

  Points: 2329

  More actions

  October 5, 2009 at 12:46 am

  #136324

  Hello,

  I have a table, which holds (besides all other data) information, when this data was inserted. Now i would like to know frequency of inserts (number of inserts per hour (or per 15 minutes)). Below is code, what I want to achieve. Note that I also want to show time interval where no inserts occured (in my example from 10:00 to 11:00).

  if object_id('tempdb..#temp') is not null drop table #temp

  create table #temp (Id int identity(1,1), Date datetime)

  insert into #temp (Date)

  select '2009-10-05 08:01:00' union all

  select '2009-10-05 08:22:00' union all

  select '2009-10-05 08:23:00' union all

  select '2009-10-05 08:24:00' union all

  select '2009-10-05 09:30:00' union all

  select '2009-10-05 11:01:00' union all

  select '2009-10-05 11:05:00' union all

  select '2009-10-05 11:52:00' union all

  select '2009-10-05 12:01:00' union all

  select '2009-10-05 12:05:00' union all

  select '2009-10-05 12:07:00' union all

  select '2009-10-05 12:09:00' union all

  select '2009-10-05 12:20:00' union all

  select '2009-10-05 12:30:00' union all

  select '2009-10-05 12:40:00' union all

  select '2009-10-05 12:50:00' union all

  select '2009-10-05 12:55:00' union all

  select '2009-10-05 13:30:00' union all

  select '2009-10-05 13:35:00' union all

  select '2009-10-05 13:40:00' union all

  select '2009-10-05 14:01:00'

  -- I want to get this data

  select '08:00 - 09:00', 4 union all

  select '09:00 - 10:00', 1 union all

  select '10:00 - 11:00', 0 union all

  select '11:00 - 12:00', 3 union all

  select '12:00 - 13:00', 9 union all

  select '13:00 - 14:00', 3 union all

  select '14:00 - 15:00', 1

• Sarab_SQLChamp

  Hall of Fame

  Points: 3882

  More actions

  October 5, 2009 at 2:04 am

  #1061583

  You can achive this with the help of Insert trigger on the table where you can log the insert along with a timestamp column.

  You can query the triggered table afterwards to get both reports accordingly.

  or if you can change the table defination just add a new column with timestamp and it can also solve the same purpose.

  Regards,
  Sarabpreet Singh
  Sarabpreet.com
  SQLChamp.com
  Twitter: @Sarab_SQLGeek

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  October 5, 2009 at 2:23 am

  #1061586

  Try this for starters...

  select Hour = number,

  count(date)

  from master..spt_values left outer join #temp

  on datepart(hh,date)= number

  where TYPE='p' and number between 0 and 23

  group by number

  order by 1

  ---

  
  Clear Sky SQL
  My Blog[/url]

• eharper

  SSC Eights!

  Points: 802

  More actions

  October 5, 2009 at 2:43 am

  #1061595

  Link to cross post on Stackoverflow