# 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

--&gt; 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```