Re: Call volume query

On 2009-01-29, Mike Diehl <mdiehl(at)diehlnet(dot)com> wrote:
> Hi all.
>
> I've encountered an SQL problem that I think is beyond my skills...
>
> I've got a table full of records relating to events (phone calls, in
> this case) and I need to find the largest number of events (calls)
> occurring at the same time.

one time when this occurred time this happened will be immediately
after the start of one of the calls.

> The table had a start timestamp and a duration field which contains the
> length of the call in seconds.
>
> I need to find out how many concurrent calls I supported, at peek
> volume.
>
> Can this be done in SQL? Or do I need to write a perl script?

yes. but possibly not efficiently .

something like this?

SELECT c.start, COUNT(*) as foo
FROM calls as c
JOIN calls as d
ON d.start <= c.start
AND d.duration >= (c.start - d.start)
GROUP BY c.start
ORDER BY foo DESC,c.start DESC
LIMIT 1

it is almost certainly be possible do this more efficiently with a
custom agregate function. O(n log(n)) instead of O(n^2)