Timeslice Temp Table In SQL, Useful For Joining From And Reporting Counts Of Stuff

• Tweet
• 

        // @table full of timeslices

declare @TimeSlices table
(
	StartTime datetime,
	EndTime datetime
)
declare @StartTime datetime
declare @EndTime datetime
declare @IncrementMinutes int

set @StartTime  =	'2007-08-06 17:00:00.000'
set @EndTime  =		'2007-08-07 08:00:00.000'
set @Incrementminutes  = 60


while (1=1)
begin
	insert into @TimeSlices
	select @StartTime, dateadd(mi, @IncrementMinutes, @StartTime)

	set @StartTime = dateadd(mi, @IncrementMinutes, @StartTime)
	if (@StartTime > @EndTime) BREAK
end

select	count(TransmissionAttemptId) as FilesSent, 
		StartTime as StartTime, 
		dateadd(mi, @IncrementMinutes, StartTime) as EndTime
	from @TimeSlices T
	left join TransmissionAttempt TA
	on	TA.SentAt > T.StartTime
		and 
		TA.SentAt < T.EndTime
	group by T.StartTime

Tags:
• t-sql