Maximum number of simultaneous events

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Maximum number of simultaneous events

Author

Topic

ds9
Starting Member

38 Posts

Posted - 2008-10-13 : 09:38:18

Hi there
I have a table which tracks occurrences of a given event:

EventInstance|EventStart|EventFinish
1|2008-10-1 18:44:33.322|2008-10-1 18:48:23.322
2|2008-10-1 18:45:33.322|2008-10-1 18:46:23.322
3|2008-10-1 18:46:33.322|2008-10-1 18:50:23.322
...

Is it possible to make a query to get the maximum number of simultaneous events across the entire table?
I must confess that I don't even know what approach to take on this

Thanks!
ds9

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 09:41:16

it is possible. But what will be input from user? Or what time range should be considered for finding out simultaneous events? 1 hr interval? or a day? or more?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 09:48:50

for example, the below pocedure will give number of simultaneous events events happening per hour for the whole day for the entered date value

CREATE PROC SimultaneousEvents
@Start datetime
AS
SELECT DATEADD(hh,v.number,@Start),t.EventCount
FROM master..spt_values v
CROSS APPLY (SELECT COUNT(EventInstance) AS EventCount
             FROM YourTable
             WHERE DATEADD(hh,v.number,@Start) BETWEEN EventStart    AND EventFinish) t
WHERE v.type='p'
AND DATEADD(hh,v.number,@Start) <= DATEADD(dd,1,@Start)
GO

ds9
Starting Member

38 Posts

Posted - 2008-10-13 : 09:50:56

The table typically stores 4 or 5 days of events, so if I could get the maximum simultaneous events per day it would be great.

thanks
ds9

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 09:58:52

modify like below. this will take two date values and give max number of simultaneous events happening per day between two dates.

CREATE PROC SimultaneousEvents
@Start datetime,
@End datetime
AS
SELECT DATEADD(dd,DATEDIFF(dd,0,Date),0) AS Day,MAX(EventCount) AS MaxSimultaneousEvents
FROM
(
SELECT DATEADD(hh,v.number,@Start) AS Date,t.EventCount
FROM master..spt_values v
CROSS APPLY (SELECT COUNT(EventInstance) AS EventCount
             FROM YourTable
             WHERE DATEADD(hh,v.number,@Start) BETWEEN EventStart    AND EventFinish) t
WHERE v.type='p'
AND DATEADD(hh,v.number,@Start) <= @End
)t
GROUP BY DATEADD(dd,DATEDIFF(dd,0,Date),0)
GO

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-10-15 : 05:26:17

I'm not keen on Visakh's solution: it's only sampling at each hour. To find transient peaks, it would need to sample every time an event starts (and, if no events start in a reporting period, at the start of each reporting period too... although this is probably easier to work out afterwards).

However, if you count the events active at the start of each event then the query will become very slow for more than a couple of thousand events as it's having to compare a time to many time ranges.

Here's a different approach. This code doesn't include reporting periods that don't contain any event starts or finishes. Apologies for the calculation of s_hi being a bit magical!


CREATE TABLE dbo.Event (
  id int PRIMARY KEY NOT NULL,
  start datetime NOT NULL,
  finish datetime NOT NULL,
  CHECK (start <= finish)
)

-- Create nearly 100000 events over 5 days: allow some events to start
-- up to an hour before the 20081001 so we don't start that day at zero.
-- Duration of events is up to 1 hour with skewed distribution
-- Only include events that overlap the 5 day period.
-- Requires dbo.Numbers tally table (or view or table-valued function)
-- of at least 100000 rows.
INSERT INTO dbo.Event
SELECT id, start, finish
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY start) AS id, start, DATEADD(ms, duration, start) AS finish
    FROM (
        SELECT
          DATEADD(ms, RAND(CAST(NEWID() AS binary(4))) *
            (5 * 24 + 1) * 3600000 + n*0, '20080930 23:00') AS start,
          RAND(CAST(NEWID() AS binary(4))) *
            RAND(CAST(NEWID() AS binary(4))) *
            RAND(CAST(NEWID() AS binary(4))) *
            RAND(CAST(NEWID() AS binary(4))) *
            1 * 3600000 + n*0 AS duration
        FROM dbo.Numbers
        WHERE n >= 0 AND n < 100000
      ) AS A
  ) AS A
-- just to make it obvious how many events fall outside the reporting period:
WHERE finish >= '20081001' AND start < '20081006'

-- Create separate indexes on start and finish times:
CREATE INDEX Event_start ON dbo.Event(start)
CREATE INDEX Event_finish ON dbo.Event(finish)

-- For each event start and end time find the number of simultaneous
-- events at that time. Filter out any events that don't occur in the 5 day
-- period. Group for maximum in day.
SELECT event_day, MAX(s_hi) AS max_simultaneous
FROM (
    SELECT DATEADD(day, DATEDIFF(day, 0, t), 0) AS event_day,
      d * (rd + rd - ROW_NUMBER() OVER (ORDER BY t, d)) - (d+1)/2 AS s_hi
    FROM (
        SELECT 1 AS d, start AS t, ROW_NUMBER() OVER (ORDER BY start) AS rd
        FROM dbo.Event

        UNION ALL

        SELECT -1 AS d, finish AS t, ROW_NUMBER() OVER (ORDER BY finish) AS rd
        FROM dbo.Event
      ) AS A
  ) AS A
WHERE event_day BETWEEN '20081001' AND '20081005'
GROUP BY event_day
ORDER BY event_day
GO

DROP TABLE dbo.Event

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources