Alternative approach to calculate most used time interval

Fri Jan 30, 2009 by Peter Larsson in optimization, sql-server-2008, algorithms, sql-server-2005

In the past I have given the advice to break down all date intervals into the smallest part, most often minutes.
Then OP should group by the minute.

-- Prepare sample data
DECLARE     @Data TABLE
            (
                         RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
                         CreateDate DATETIME,
                         DeleteDate DATETIME
            )

-- Populate sample data
INSERT      @Data
            (
                         CreateDate,
                         DeleteDate
            )
SELECT      '2009-01-14 22:33', '2009-01-14 22:35' UNION ALL
SELECT      '2009-01-14 22:33', '2009-01-14 22:33' UNION ALL
SELECT      '2009-01-14 22:34', '2009-01-14 22:35' UNION ALL
SELECT      '2009-01-14 22:35', '2009-01-14 22:35' UNION ALL
SELECT      '2009-01-14 22:35', '2009-01-14 22:36' UNION ALL
SELECT      '2009-01-14 22:37', '2009-01-14 22:37' UNION ALL
SELECT      '2009-01-14 22:39', '2009-01-14 22:39' UNION ALL
SELECT      '2009-01-14 22:38', '2009-01-14 22:38' UNION ALL
SELECT      '2009-01-14 22:39', '2009-01-14 22:39' UNION ALL
SELECT      '2009-01-14 22:41', '2009-01-14 22:41' UNION ALL
SELECT      '2009-01-14 22:43', '2009-01-14 22:44' UNION ALL
SELECT      '2009-01-14 22:52', '2009-01-14 22:52' UNION ALL
SELECT      '2009-01-14 22:53', '2009-01-14 22:53' UNION ALL
SELECT      '2009-01-14 22:53', '2009-01-14 22:53' UNION ALL
SELECT      '2009-01-14 22:56', '2009-01-14 22:57' UNION ALL
SELECT      '2009-01-14 22:57', '2009-01-14 22:57' UNION ALL
SELECT      '2009-01-14 22:58', '2009-01-14 22:58' UNION ALL
SELECT      '2009-01-14 22:58', '2009-01-14 22:59' UNION ALL
SELECT      '2009-01-14 22:59', '2009-01-14 22:59'

DECLARE     @From INT,
            @To INT

SELECT      @From = MIN(DATEDIFF(MINUTE, 0, CreateDate)),
            @To = MAX(DATEDIFF(MINUTE, 0, DeleteDate))
FROM        @Data

SELECT      w.theTime,
            COUNT(*)
FROM        (
                        SELECT      DATEADD(MINUTE, Number + @From, 0) AS theTime
                        FROM        master..spt_values
                        WHERE       Type = 'P'
                                     AND Number <= @To - @From
            ) AS w
INNER JOIN @Data AS d ON d.CreateDate <= w.theTime
                        AND d.DeleteDate >= w.TheTime
GROUP BY    w.theTime
ORDER BY    COUNT(*) DESC,
            w.theTime DESC

Well, sometimes the time interval is too large to hold all minute values, and what if OP all of a sudden decides to break down the calculation into milliseconds?

The problem resembles about date range searching. Most people use

SELECT      *
FROM        Table1
WHERE       Col1 BETWEEN '20090129 00:00:00.000' AND '20090129 23:59:59.997'

What you should use is open-ended search criteria as

SELECT      *
FROM        Table1
WHERE       Col1 >= '20090129'
            AND Col1 < '20090130'

The solution for this type of problem can then look something like this

-- Prepare sample data

DECLARE @Data TABLE

(

RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

CreateDate DATETIME,

DeleteDate DATETIME

)

-- Populate sample data

INSERT @Data

(

CreateDate,

DeleteDate

)

SELECT '2009-01-14 22:33:41.857', '2009-01-14 22:35:59.543' UNION ALL

SELECT '2009-01-14 22:33:42.857', '2009-01-14 22:33:59.543' UNION ALL

SELECT '2009-01-14 22:34:26.513', '2009-01-14 22:35:43.233' UNION ALL

SELECT '2009-01-14 22:35:14.920', '2009-01-14 22:35:31.530' UNION ALL

SELECT '2009-01-14 22:35:50.373', '2009-01-14 22:36:07.340' UNION ALL

SELECT '2009-01-14 22:37:26.793', '2009-01-14 22:37:44.857' UNION ALL

SELECT '2009-01-14 22:39:22.077', '2009-01-14 22:39:38.543' UNION ALL

SELECT '2009-01-14 22:38:03.873', '2009-01-14 22:38:20.827' UNION ALL

SELECT '2009-01-14 22:39:39.247', '2009-01-14 22:39:55.840' UNION ALL

SELECT '2009-01-14 22:41:25.857', '2009-01-14 22:41:42.467' UNION ALL

SELECT '2009-01-14 22:43:14.607', '2009-01-14 22:44:31.483' UNION ALL

SELECT '2009-01-14 22:52:10.233', '2009-01-14 22:52:26.827' UNION ALL

SELECT '2009-01-14 22:53:08.187', '2009-01-14 22:53:24.983' UNION ALL

SELECT '2009-01-14 22:53:36.483', '2009-01-14 22:53:53.060' UNION ALL

SELECT '2009-01-14 22:56:56.403', '2009-01-14 22:57:13.263' UNION ALL

SELECT '2009-01-14 22:57:28.247', '2009-01-14 22:57:44.780' UNION ALL

SELECT '2009-01-14 22:58:16.090', '2009-01-14 22:58:32.623' UNION ALL

SELECT '2009-01-14 22:58:52.137', '2009-01-14 22:59:08.670' UNION ALL

SELECT '2009-01-14 22:59:21.170', '2009-01-14 22:59:37.733'

-- Prepare staging table

DECLARE @Stage TABLE

(

RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

FromTime DATETIME NOT NULL,

ToTime DATETIME NOT NULL

)

-- Populate staging table

INSERT @Stage

(

FromTime,

ToTime

)

SELECT u.theTime,

u.theTime

FROM @Data AS d

UNPIVOT (

theTime

FOR theCol IN(d.CreateDate, d.DeleteDate)

) AS u

GROUP BY u.theTime

ORDER BY u.theTime

-- Update with closest range

UPDATE s

SET s.ToTime = w.FromTime

FROM @Stage AS s

INNER JOIN @Stage AS w ON w.RecID = s.RecID + 1

-- Delete last time

DELETE

FROM @Stage

WHERE RecID = SCOPE_IDENTITY()

-- Display the result

SELECT s.FromTime,

s.ToTime,

COUNT(*) AS Occurencies

FROM @Data AS d

INNER JOIN @Stage AS s ON s.FromTime < d.DeleteDate

AND s.ToTime > d.CreateDate

GROUP BY s.FromTime,

s.ToTime

HAVING COUNT(*) > 1

ORDER BY COUNT(*) DESC,

s.FromTime DESC

Finally, there is also a twist with this approach. You do not only get for which exact minute there is the most concurrency, you get the whole range!

And you can also see the gaps, where no concurrency occurs at all.

-- Display the gaps

SELECT s.FromTime,

s.ToTime,

COUNT(d.CreateDate) AS Occurencies

FROM @Data AS d

RIGHT JOIN @Stage AS s ON s.FromTime < d.DeleteDate

AND s.ToTime > d.CreateDate

GROUP BY s.FromTime,

s.ToTime

HAVING COUNT(d.CreateDate) = 0

ORDER BY COUNT(d.CreateDate) DESC,

s.FromTime