Finding simultaneous events in a database between times

Question

I have a database that stores phone call records. Each phone call record has a start time and an end time. I want to find out what is the maximum amount of phone calls that are simultaneously happening in order to know if we have exceed the amount of available phone lines in our phone bank. How could I go about solving this problem?

Answer 1

SELECT COUNT(*) FROM calls 
    WHERE '2010-06-15 15:00:00' BETWEEN calls.starttime AND calls.endtime

and repeat this for every second.

Answer 2

The only practical method I can think of is as follows:

Split the period you want to analyze in arbitrary "buckets", say, 24 1-hour buckets over the day. For each Bucket count how many calls either started or finished between the start or the end of the interval

Note that the 1-hour limit is not a hard-and-fast rule. You could make this shorter or longer, depending on how precise you want the calculation to be. You could make the actual "length" of the bucket a function of the average call duration. So, let's assume that your average call is 3 minutes. If it is not too expensive in terms of calculations, use buckets that are 3 times longer than your average call (9 minutes) this should be granular enough to give precise results.

Answer 3

Given the fact that the maximum number of connections is going to be a StartTime points, you can

SELECT TOP 1 count(*) as CountSimultaneous
FROM PhoneCalls T1, PhoneCalls T2
WHERE
     T1.StartTime between T2.StartTime and T2.EndTime
GROUP BY
     T1.CallID
ORDER BY CountSimultaneous DESC

The query will return for each call the number of simultaneous calls. Either order them descending and select first one or SELECT MAX(CountSimultaneous) from the above (as subquery without ordering and without TOP).

Answer 4

try this:

DECLARE @Calls table (callid int identity(1,1), starttime datetime, endtime datetime)
INSERT @Calls (starttime,endtime) values ('6/12/2010 10:10am','6/12/2010 10:15am')
INSERT @Calls (starttime,endtime) values ('6/12/2010 11:10am','6/12/2010 10:25am')
INSERT @Calls (starttime,endtime) values ('6/12/2010 12:10am','6/12/2010 01:15pm')
INSERT @Calls (starttime,endtime) values ('6/12/2010 11:10am','6/12/2010 10:35am')
INSERT @Calls (starttime,endtime) values ('6/12/2010 12:10am','6/12/2010 12:15am')
INSERT @Calls (starttime,endtime) values ('6/12/2010 10:10am','6/12/2010 10:15am')


DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate='6/12/2010'
      ,@EndDate='6/13/2010'
;with AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DATEADD(second,1,DateOf) AS DateOf
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT
    a.DateOf,COUNT(c.callid) AS CountOfCalls
    FROM AllDates           a
        INNER JOIN @Calls   c ON a.DateOf>=c.starttime and a.DateOf<=c.endtime
    GROUP BY a.DateOf
    ORDER BY 2 DESC
    OPTION (MAXRECURSION 0)

OUTPUT:

DateOf                  CountOfCalls
----------------------- ------------
2010-06-12 10:10:00.000 3
2010-06-12 10:10:01.000 3
2010-06-12 10:10:02.000 3
2010-06-12 10:10:03.000 3
2010-06-12 10:10:04.000 3
2010-06-12 10:10:05.000 3
2010-06-12 10:10:06.000 3
2010-06-12 10:10:07.000 3
2010-06-12 10:10:08.000 3
2010-06-12 10:10:09.000 3
2010-06-12 10:10:10.000 3
2010-06-12 10:10:11.000 3
2010-06-12 10:10:12.000 3
2010-06-12 10:10:13.000 3
2010-06-12 10:10:14.000 3
2010-06-12 10:10:15.000 3
2010-06-12 10:10:16.000 3
2010-06-12 10:10:17.000 3
2010-06-12 10:10:18.000 3
2010-06-12 10:10:19.000 3
2010-06-12 10:10:20.000 3
2010-06-12 10:10:21.000 3
2010-06-12 10:10:22.000 3
2010-06-12 10:10:23.000 3
2010-06-12 10:10:24.000 3
2010-06-12 10:10:25.000 3
2010-06-12 10:10:26.000 3
2010-06-12 10:10:27.000 3
....

add a TOP 1 or put this query in a derived table and further aggergate it if necessary.