Re: Determine max concurrent calls

From: joel garry <joel-garry_at_home.com>
Date: Wed, 7 Apr 2010 13:45:01 -0700 (PDT)
Message-ID: <85b5b43c-a4d1-4692-9139-7a79b41e2e18_at_g10g2000yqh.googlegroups.com>

On Apr 7, 11:58 am, Sashi <small..._at_gmail.com> wrote:
> Hi all, I have a table with details of phone calls.
> The call date is given as DD-MMM-YYYY HH:MI:SS. I have a couple of
> other fields that give the duration as DUR_MM and DUR_SS.
> So an example record would be
> SRC_NUM, DEST_NUM, CALL_DATE, DUR_MM, DUR_SS
> 444-1212, 555-2323, 04-APR-2010, 05, 38.
>
> So this would be a call placed from 444-1212 to 555-2323 on Apr 04th,
> that lasted 5 min and 38 sec. (Other fields not mentioned for brevity
> and clarity).
>
> On a given day, I want to be able to find out the minute at which
> there are a maximum number of concurrent calls.
>
> I've quickly come to the conclusion that I can't do this via simple
> SQL. I'm new to writing procedures (though I have good experience with
> programming languages in general).
>
> Any tips/pseudo-code that give me an idea of how to proceed are
> appreciated.
>
> Thanks,
> Sashi

You may be able to do this with analytics. I'm no analytics expert, but see http://forums.oracle.com/forums/thread.jspa?threadID=1030207&tstart=105 for one similar example, and Charles Hooper explains things well with examples: http://hoopercharles.wordpress.com/2009/12/08/sql-combining-over-lapping-date-rows/ . In general it is better to keep things in the SQL engine when possible, though (I speculate) there may be cases where analytics won't be the best way. Also, I recall there is an undocumented analytic that can do something like this, stay away from undocumented things and code that is, shall we say, too cute.

The above were found in a few seconds with the following google term: oracle analytics maximum overlapping records

Tom Kyte also is a big advocate of analytics, see asktom.oracle.com

In general in this group, if you want detailed help with a coding issue, it's good to supply create table and load data statements, and what you've tried. Also necessary is exact versions (10gR2 is not a version, 10.2.0.4 is a version) for Oracle and your OS/platform. Hand people a ball and they love to run with it (or whatever your local equivalent metaphor would be).

jg

--
_at_home.com is bogus.
http://www.chulavistaca.gov/City_Services/Community_Services/Nature_Center/webcams/eaglemesacam.asp

Received on Wed Apr 07 2010 - 15:45:01 CDT