Databases
StackOverflow2013
Postsdbo
POSql Server: Count records (including zero) per ten-minute intervals
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSql Server: Count records (including zero) per ten-minute intervals
    text
    copied!<p>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.</p> <p>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.</p> <p>Here is the query so far:</p> <pre><code>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()) &lt; @time) AS I(interval) GROUP BY interval ORDER BY interval </code></pre> <p>Here is the table structure:</p> <pre><code>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 )</code></pre> <p>And here is what some of the records look like: <code><pre> 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' </pre></code></p> <p>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).</p> <p>Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload