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
) SELECT * FROM Numbers ORDER BY n;

view current version

edited dec 3 12

created dec 3 12 Mahmoud Gamal