Re: Sequential Time and Cumulative Counts

Those type of running total problems cannot be really solved efficiently with the current SQL Server editions. On a large data set all set based solution will be slow.

To generate the full time span (the breakdown by each minute) you can create a table with minutes for a range (or it could be a table valued function that returns time span based on parameters, also doable with recursive CTE). Then left joining your log table to this table with minutes will give you each minute in the range. Here is how it may look (note for the generation of minutes range the use of utility table with numbers, in this case using the system table spt_values; you can create a permanent one, some methods here http://www.projectdmx.com/tsql/tblnumbers.aspx).

Also, this will work fine when crossing hour boundaries.

DECLARE @start_time SMALLDATETIME

SET @start_time = '2008-03-27T16:50:00'

CREATE TABLE Minutes (
mi SMALLDATETIME)

INSERT INTO Minutes (mi)
SELECT DATEADD(mi, number - 1, @start_time)
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 120

SELECT mi, time_offset, MAX(cnt) AS msg_cnt
FROM (
SELECT M.mi,
DATEDIFF(mi,
@start_time,
M.mi) AS time_offset,
(SELECT COUNT(*)
FROM MailLog AS L1
WHERE L1.msgid = 2
AND L1.datesent <= DATEADD(mi, 1, M.mi)) AS cnt
FROM Minutes AS M
LEFT JOIN MailLog AS L
ON L.datesent >= M.mi
AND L.datesent < DATEADD(mi, 1, M.mi)
AND L.msgid = 2
WHERE M.mi BETWEEN @start_time
AND DATEADD(mi, 5, @start_time)) AS T
GROUP BY mi, time_offset
ORDER BY mi


Results:

mi time_offset msg_cnt
----------------------- ----------- -----------
2008-03-27 16:50:00 0 1
2008-03-27 16:51:00 1 2
2008-03-27 16:52:00 2 2
2008-03-27 16:53:00 3 4
2008-03-27 16:54:00 4 5
2008-03-27 16:55:00 5 5


HTH,

Plamen Ratchev
http://www.SQLStudio.com

.