0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/13682754/creating-time-intervals-in-mssql

Q&A for committed Christians, experts in Christianity and those interested in learning more

DECLARE @Events Table(EventId int, EventDate DATETIME);

/*
 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
*/
INSERT INTO @Events VALUES
(1, '2012-12-03 00:00:00.000'),
(1, '2012-12-03 00:02:00.000'),
(1, '2012-12-03 00:03:00.000'),
(1, '2012-12-03 00:04:00.000'),
(1, '2012-12-03 00:05:00.000'),
(1, '2012-12-03 00:10:00.000'),
(1, '2012-12-03 00:11:00.000'),
(1, '2012-12-03 00:11:00.000'),
(1, '2012-12-03 00:12:00.000'),
(1, '2012-12-03 00:13:00.000'),
(1, '2012-12-03 00:14:00.000'),
(1, '2012-12-03 00:14:00.000'),
(1, '2012-12-03 00:15:00.000'),
(1, '2012-12-03 00:15:00.000'),
(1, '2012-12-03 00:15:00.000'),
(1, '2012-12-03 00:15:00.000'),
(1, '2012-12-03 00:16:00.000'),
(1, '2012-12-03 00:17:00.000'),
(1, '2012-12-03 00:18:00.000'),
(1, '2012-12-03 00:19:00.000'),
(1, '2012-12-03 00:24:00.000'),
(1, '2012-12-03 02:00:00.000'),
(1, '2012-12-03 14:00:00.000');

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;

-- SELECT DATEADD(minute, 10, @startDate);
;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;

view current version

edited dec 3 12

created dec 3 12 Mahmoud Gamal