Finding the most active 15 minute time period


A guy over at sqlteam.com wanted to find the most active 15-minute period for a series of different stores his company runs and I thought this would be a nice topic for a blog post. I modified the problem a little to fit a sample table I had lying around and the result wasn’t too hard to come by.

Basically I have a table with sample call records for a telephone company and I want to find the most active 15-minute period where the most calls are started. First we have to generate a table with some sample data:

create table calls (
	call_id int identity(1, 1) primary key clustered,
	a_number bigint,
	b_number bigint,
	start_time datetime,
	duration int)
 go

--> Function to generate random numbers, used to create sample data
create function randomnumber (@min bigint, @max bigint, @rand float)
returns bigint
as
begin
	return CAST(((@Max + 1) - @Min) * @Rand + @Min AS bigint)
end
go

--> Generate 500  rows of sample data
declare 
	@end int = 500,
	@current int = 1
	
while (@current <= @end)
	begin
		insert into calls (a_number, b_number, start_time, duration)
		select 
			dbo.randomnumber (22000000, 22999999, RAND()),
			dbo.randomnumber (22000000, 22999999, RAND()),
			DATEADD(MINUTE, dbo.randomnumber (1, 300, RAND()),GETDATE()),
			dbo.randomnumber (3, 3600, RAND())
			
		select @current = @current + 1
	end
go

Now once the test table with sample data has been established, we can run the actual query:

DECLARE @dummydate datetime = '2010-06-23 00:00:00'

;WITH cte AS (
	SELECT 
		[Mod] = DATEDIFF(MILLISECOND, @dummydate, start_time) % (15 * 60 * 1000), --&gt; 15 mins to milliseconds
		*
	FROM calls
	)
SELECT TOP 10 
	Period = DATEADD(MILLISECOND, -[Mod], start_time), 
	CountOfTransID = COUNT(call_id)
FROM cte
GROUP BY DATEADD(MILLISECOND, -[Mod], start_time)
ORDER BY COUNT(call_id) DESC
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s