Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Alternative approach to calculate most used time interval

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
 

Print | posted on Friday, January 30, 2009 9:03 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET