THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter Larsson

Thinking outside the box

Date and Time concurrency

In the past I mostly have given the advice to break down all date intervals into the smallest part (often minutes) and then 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 you all of a sudden decides to break down the calculation into milliseconds?

The problem resembles about date range searching. Most people tend to 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

//Peso
Published Saturday, September 19, 2009 1:51 PM by Peso
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement