Timeslice temp table in SQL, useful for joining from and reporting counts of stuff (See related posts)

   1  
   2  
   3  
   4  declare @TimeSlices table
   5  (
   6  	StartTime datetime,
   7  	EndTime datetime
   8  )
   9  declare @StartTime datetime
  10  declare @EndTime datetime
  11  declare @IncrementMinutes int
  12  
  13  set @StartTime  =	'2007-08-06 17:00:00.000'
  14  set @EndTime  =		'2007-08-07 08:00:00.000'
  15  set @Incrementminutes  = 60
  16  
  17  
  18  while (1=1)
  19  begin
  20  	insert into @TimeSlices
  21  	select @StartTime, dateadd(mi, @IncrementMinutes, @StartTime)
  22  
  23  	set @StartTime = dateadd(mi, @IncrementMinutes, @StartTime)
  24  	if (@StartTime > @EndTime) BREAK
  25  end
  26  
  27  select	count(TransmissionAttemptId) as FilesSent, 
  28  		StartTime as StartTime, 
  29  		dateadd(mi, @IncrementMinutes, StartTime) as EndTime
  30  	from @TimeSlices T
  31  	left join TransmissionAttempt TA
  32  	on	TA.SentAt > T.StartTime
  33  		and 
  34  		TA.SentAt < T.EndTime
  35  	group by T.StartTime