Sql Server: Count records (including zero) per ten-minute intervals

sql-serversql-server-2005

I currently have a table (SQL Server 2005) that logs the visits against my web app, and I want to put together some code to report (and display a visualization) of that traffic. What I want is to display the number of visits during each ten-minute interval over the last 24 hours.

I have a query that does just that, but there are ten-minute intervals during which there are no visits, and I would like to adjust my query to display a zero count for those intervals. I imagine I could come up with something using cursors, but I'd rather not use them if I can avoid it.

Here is the query so far:

DECLARE @time int
DECLARE @interval int
SELECT @time=96
SELECT @interval=10
SELECT interval,
COUNT(*) AS requestCount,
DATEDIFF(MINUTE,DATEADD(HOUR,-1*@time-1,getDate()),interval)/@interval AS intervalPos
FROM 
(SELECT DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, getDate(), requestBegin) / (@interval*1.0)) * @interval,getDate())
FROM [track_pageSubmit] WHERE requestBegin IS NOT NULL AND DATEDIFF(HOUR,requestBegin,getDate()) < @time) AS I(interval) 
GROUP BY interval ORDER BY interval

Here is the table structure:

CREATE TABLE [dbo].[Track_PageSubmit](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [popid] [int] NOT NULL,
    [section] [varchar](30) NULL,
    [page] [int] NULL,
    [requestBegin] [datetime] NULL,
    [requestEnd] [datetime] NULL,
    [rendered] [datetime] NULL,
    [postBegin] [datetime] NULL,
    [postEnd] [datetime] NULL
)

And here is what some of the records look like:

INSERT INTO track_pageSubmit (popid,section,page,requestbegin,requestend,rendered,postbegin,postend)
SELECT '2393712','Main_Can_Eng','10','2010-01-22 10:22:08.287','2010-01-22 10:22:08.330',NULL,'2010-01-22 10:22:09.503','2010-01-22 10:22:09.627' UNION
SELECT '2393712','Main_Can_Eng','11','2010-01-22 10:22:09.660','2010-01-22 10:22:09.770',NULL,'2010-01-22 10:22:10.973','2010-01-22 10:22:11.050' UNION
SELECT '2393712','Main_Can_Eng','12','2010-01-22 10:22:11.080','2010-01-22 10:22:11.143',NULL,'2010-01-22 10:22:12.503','2010-01-22 10:22:12.567' UNION
SELECT '2394478','main','21','2010-01-21 10:38:54.057','2010-01-21 10:38:54.117','2010-01-21 10:38:54.487','2010-01-21 10:38:55.633','2010-01-21 10:38:55.697' UNION
SELECT '2394478','main','22','2010-01-21 10:38:55.757','2010-01-21 10:38:55.820','2010-01-21 10:38:56.197','2010-01-21 10:38:57.477','2010-01-21 10:38:57.570' UNION
SELECT '2394478','main','23','2010-01-21 10:38:57.617','2010-01-21 10:38:57.993','2010-01-21 10:38:58.367','2010-01-21 10:38:59.397','2010-01-21 10:38:59.493' UNION
SELECT '2394478','main','25','2010-01-21 10:38:59.553','2010-01-21 10:38:59.617','2010-01-21 10:38:59.993','2010-01-21 10:39:01.227','2010-01-21 10:39:01.303' UNION
SELECT '2394478','main','26','2010-01-21 10:39:01.350','2010-01-21 10:39:01.477','2010-01-21 10:39:01.860','2010-01-21 10:39:02.787','2010-01-21 10:39:02.867' UNION
SELECT '2394478','main','27','2010-01-21 10:39:02.930','2010-01-21 10:39:03.007','2010-01-21 10:39:03.400','2010-01-21 10:39:04.147','2010-01-21 10:39:04.460' UNION
SELECT '2394478','main','28','2010-01-21 10:39:04.507','2010-01-21 10:39:05.147','2010-01-21 10:39:05.790','2010-01-21 10:39:19.413','2010-01-21 10:39:19.477' UNION
SELECT '2393754','exp46_cex','1','2010-01-22 12:40:56.563','2010-01-22 12:40:56.640',NULL,'2010-01-22 12:40:58.657','2010-01-22 12:40:58.733' UNION
SELECT '2393754','exp46_cex','2','2010-01-22 12:40:58.750','2010-01-22 12:40:58.780',NULL,'2010-01-22 12:41:15.623','2010-01-22 12:41:15.657' UNION
SELECT '2393754','additionalComments','1','2010-01-22 12:41:15.670','2010-01-22 12:41:15.733',NULL,'2010-01-22 12:41:19.000','2010-01-22 12:41:19.030' UNION
SELECT '2393802','main','2','2010-01-22 12:44:50.857','2010-01-22 12:44:50.933',NULL,'2010-01-22 12:44:53.497','2010-01-22 12:44:53.557' UNION
SELECT '2393802','main','3','2010-01-22 12:44:53.590','2010-01-22 12:44:53.667',NULL,'2010-01-22 12:44:56.370','2010-01-22 12:44:56.730'

Bonus points (in the form of extra thanks from me) to anyone who can change the query so that I can also report distinct popids per interval (in addition to total requests).

Thanks!

Best Solution

-- Because your sample data spans 27 hours:

DECLARE
    @hours TINYINT,
    @minute_interval TINYINT,
    @start SMALLDATETIME;

SELECT
    @hours = 27,
    @minute_interval = 10,
    @start = '20100122 13:00';

;WITH x AS 
(
    SELECT TOP (@hours * (60 / @minute_interval))
        n = ROW_NUMBER() OVER
        (ORDER BY column_id)
    FROM msdb.sys.columns
),
intervals(boundary) AS
(
    SELECT CONVERT
    (
      SMALLDATETIME,
      DATEADD(MINUTE, (-n * @minute_interval), @start)
    )
    FROM x
)
SELECT
    i.boundary,
    RequestCount = COUNT(d.id),
    DistinctPopIDs = COUNT(DISTINCT d.popid)
FROM
    intervals AS i
LEFT OUTER JOIN
    dbo.Track_PageSubmit AS d
    ON d.requestBegin >= i.boundary
    AND d.requestBegin < DATEADD(MINUTE, @minute_interval, i.boundary)
GROUP BY i.boundary
ORDER BY i.boundary;
Related Question