Creating time intervals in SQL Server

Question

I have a table in SQL Server that stores events and the datetime of the event. I am now looking to have these events grouped into e.g. 10 minute intervals showing how many events in each group.

I am looking for a result similar to the following. Does anyone have an example og how this could be done?

    FromTIme                | ToTime                  | Number of Events
    -----------------------------------------------------------------------
    2012-12-03 00:00:00.000 | 2012-12-03 00:10:00.000 |     5
    2012-12-03 00:10:00.000 | 2012-12-03 00:20:00.000 |    15

The query will be used in a report so I would like to be able to set the @StartDate and @EndDate of the interval.

Answer 1

You can do this:

DECLARE @startDate DATETIME = '2012-12-03 00:00:00.000';
DECLARE @endDate   DATETIME = '2012-12-03 00:20:00.000';
DECLARE @counter INT = 1;
DECLARE @nOfIntervals INT = DATEDIFF(minute, @startDate, @endDate) / 10;

;WITH Temp
AS
(
    SELECT n
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) Nums(n)
), Numbers
AS
(
    SELECT n = t2.n * 10 + t1.n + 0
    FROM Temp t1
    CROSS JOIN Temp t2
), FromToCTE
AS
(
    SELECT  
      DATEADD(minute, 10 * n, @startDate) FromDate, 
      DATEADD(minute, 10 *(n +1), @startDate) ToDate
    FROM numbers 
    WHERE (n + 1) <= @nOfIntervals
 ),EventsFromTo
 AS
 (
     SELECT 
       c.FromDate,
       c.ToDate,
       e.EventDate
     FROM @Events e
     INNER JOIN FromToCTE c ON e.EventDate BETWEEN c.FromDate AND c.ToDate
 )  
 SELECT FromDate, ToDate, COUNT(*) "Number of Events"
 FROM EventsFromTo
 GROUP BY FromDate, ToDate;

Live Demo

This will give you something like:

     FromDate                 ToDate          Number of Events
2012-12-03 00:00:00     2012-12-03 00:10:00          5
2012-12-03 00:10:00     2012-12-03 00:20:00          15 

How does this work?

I used a temp table with the numbers from 0 to 9 as an anchor table:

SELECT n
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) Nums(n)

To generate number from 0 to 99:

Numbers
AS
(
    SELECT n = t2.n * 10 + t1.n + 0
    FROM Temp t1
    CROSS JOIN Temp t2
 )

I used this table Numbers for generating the time intervals from the @startDate to @endDate, I assumed that the difference between the two dates won't exceed 99 minutes intervals. If you want to make your query work for more ranges greater than 99, just cross join the table temp more times to generate more numbers. Later I used these generated numbers to generate the datetimes intervals with 10 minutes interval between each of them. After that I joined this generated table with the Events table on the event date field. With a GROUP BY and COUNT we are done.

Note that: I didn't check whether the date difference passed to the query using the two parameters @startDate and @EndDate are dividable by 10 minutes or not, I think you might need to check this in your case, and I will leave it as a homework for you.

This might be a lot of work, but I didn't find an easy way for doing this without using a cursor