PLZ HELP: number of calls at a time

I have a traffic table which contains the following field:

START_DATE_TIME (e.g. 2/14/2006 7:19:54 AM)

and I want to the following;

how many calls we have every minute (count) starting from 00:00 to 23:59?

what's the maximam number of calls and what time was it?

-- Create tets table

create table #trafic(

id int identity not null primary key,

start datetime

)

go

--Insert sample data

insert into #trafic values(getdate())

insert into #trafic values(getdate())

insert into #trafic values(dateadd(hour, 1, getdate()))

insert into #trafic values(dateadd(hour, 2, getdate()))

insert into #trafic values(dateadd(hour, 3, getdate()))

insert into #trafic values(dateadd(minute, 10, getdate()))

insert into #trafic values(dateadd(minute, 10, getdate()))

--Show my sample date

select * from #trafic

--Result

id start

-- --

1 2007-03-06 11:14:28.983

2 2007-03-06 11:14:29.000

3 2007-03-06 12:14:29.000

4 2007-03-06 13:14:29.000

5 2007-03-06 14:14:29.000

6 2007-03-06 11:24:29.000

7 2007-03-06 11:24:29.000

--Calls per minute

select count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2)) --Expression for getting hour:minute part of call datetime

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

--Result

-- --

2 11:14

2 11:24

1 12:14

1 13:14

1 14:14

(5 row(s) affected)

--Show max calls

select top 1 count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

order by count(id) desc

--Results

-- --

2 11:24

(1 row(s) affected)

--If few minutes have maximuns call number

select top 1 with ties count(id)

, cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

from #trafic

group by cast(datepart(hour,start) as varchar(2))+':'+cast( datepart(minute,start) as varchar(2))

order by count(id) desc

--Result

-- --

2 11:14

2 11:24

(2 row(s) affected)