Sequential Time and Cumulative Counts



The SQL below represents a problem I'm trying to solve, and my current
best-shot at solving it myself. I have a table with time stamps. I
need to produce a report showing, minute-by-minute, the cumulative
total number of messages sent up to that time.

What's missing from my current solution is how to return data for
minutes when no messages are sent (in the example data bellow, no
records exist for 16:52, and as a result I don't have a row for that
in my output). Additionally, my solution will break when the time
stamps wrap at the end of an hour.

Any help is greatly appreciated!

create table maillog (msgID int, datesent datetime)
insert maillog values(1,'2008-03-26 10:00:00.000')
insert maillog values(2,'2008-03-27 16:50:10.000')
insert maillog values(2,'2008-03-27 16:51:30.000')
insert maillog values(2,'2008-03-27 16:53:12.000')
insert maillog values(2,'2008-03-27 16:53:20.000')
insert maillog values(2,'2008-03-27 16:54:10.000')
/*
problem: given a msgID, produce a report like the following: (note:
time offset is the number of minutes since the first timestamp for
that message)
TimeOffset Total Messages Sent
0 1
1 2
2 2
3 4
4 5

So, at 0 minutes (time = 16:50) the first message was sent. At 1
minute (time = 16:51) another message was sent, for a total of 2.
etc.
*/

select datePart(mi,datesent) as TimeOffset,
(
select count(*)
from maillog
where datePart(mi,datesent) <= datePart(mi,m.datesent)
and msgID = 2
) as TotalMessagesSent
from maillog m
where msgID = 2
group by datePart(mi,datesent)

drop table maillog
.