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), --> 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