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