SQL query for cumulative frequency of list of datetimes

Question

I have a list of times in a database column (representing visits to a website).

I need to group them in intervals and then get a 'cumulative frequency' table of those dates.

For instance I might have:

9:01
9:04
9:11
9:13
9:22
9:24
9:28

and i want to convert that into

9:05 - 2
9:15 - 4
9:25 - 6
9:30 - 7

How can I do that? Can i even easily achieve this in SQL? I can quite easily do it in C#

Answer 1

create table accu_times (time_val datetime not null, constraint pk_accu_times primary key (time_val));
go

insert into accu_times values ('9:01');
insert into accu_times values ('9:05');
insert into accu_times values ('9:11');
insert into accu_times values ('9:13');
insert into accu_times values ('9:22');
insert into accu_times values ('9:24');
insert into accu_times values ('9:28'); 
go

select rounded_time,
    (
    select count(*)
    from accu_times as at2
    where at2.time_val <= rt.rounded_time
    ) as accu_count
from (
select distinct
  dateadd(minute, round((datepart(minute, at.time_val) + 2)*2, -1)/2,
    dateadd(hour, datepart(hour, at.time_val), 0)
  ) as rounded_time
from accu_times as at
) as rt
go

drop table accu_times

Results in:

rounded_time            accu_count
----------------------- -----------
1900-01-01 09:05:00.000 2
1900-01-01 09:15:00.000 4
1900-01-01 09:25:00.000 6
1900-01-01 09:30:00.000 7

Answer 2

I should point out that based on the stated "intent" of the problem, to do analysis on visitor traffic - I wrote this statement to summarize the counts in uniform groups.

To do otherwise (as in the "example" groups) would be comparing the counts during a 5 minute interval to counts in a 10 minute interval - which doesn't make sense.

You have to grok to the "intent" of the user requirement, not the literal "reading" of it. :-)

    create table #myDates
       (
       myDate       datetime
       );
    go

    insert into #myDates values ('10/02/2008 09:01:23');
    insert into #myDates values ('10/02/2008 09:03:23');
    insert into #myDates values ('10/02/2008 09:05:23');
    insert into #myDates values ('10/02/2008 09:07:23');
    insert into #myDates values ('10/02/2008 09:11:23');
    insert into #myDates values ('10/02/2008 09:14:23');
    insert into #myDates values ('10/02/2008 09:19:23');
    insert into #myDates values ('10/02/2008 09:21:23');
    insert into #myDates values ('10/02/2008 09:21:23');
    insert into #myDates values ('10/02/2008 09:21:23');
    insert into #myDates values ('10/02/2008 09:21:23');
    insert into #myDates values ('10/02/2008 09:21:23');
    insert into #myDates values ('10/02/2008 09:26:23');
    insert into #myDates values ('10/02/2008 09:27:23');
    insert into #myDates values ('10/02/2008 09:29:23');
    go

    declare @interval int;
    set @interval = 10;

    select
       convert(varchar(5), dateadd(minute,@interval - datepart(minute, myDate) % @interval, myDate), 108) timeGroup,
       count(*)
    from
       #myDates
    group by
       convert(varchar(5), dateadd(minute,@interval - datepart(minute, myDate) % @interval, myDate), 108)

retuns:

timeGroup             
--------- ----------- 
09:10     4           
09:20     3           
09:30     8