DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

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

08.07.2007
| 3742 views |
  • submit to reddit
        // @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