Re: Determine max concurrent calls

From: ddf <oratune_at_msn.com>
Date: Wed, 7 Apr 2010 20:41:45 -0700 (PDT)
Message-ID: <40c92a2e-aae0-444a-9600-d244196d5ba8_at_z7g2000yqb.googlegroups.com>

On Apr 7, 2:58 pm, 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

Is this what you had in mind:

SQL> create table call_test(incoming varchar2(12),

  2                          outgoing varchar2(12),
  3                          call_start date,
  4                          call_end date)

  5 tablespace tools;

Table created.

SQL>
SQL> begin

  2          for i in 1..9999 loop
  3                  insert into call_test
  4                  values('444-555-'||rpad(1,4,'0'),
  5                         '777-345-'||lpad(i,4,'0'),
  6                          sysdate+(mod(i,13)/86400),
  7                          sysdate+((97*i)/86400));
  8
  9          end loop;
 10          commit;

 11 end;
 12 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Call volume per minute
SQL> --
SQL> select
  2          to_char(call_start, 'DD-MON-RRRR HH24:MI') start_time,
  3          count(*)  call_vol
  4  from
  5          call_test

  6 group by to_char(call_start, 'DD-MON-RRRR HH24:MI');

START_TIME                CALL_VOL
----------------------- ----------
07-APR-2010 23:39             1113
07-APR-2010 23:40             8886

SQL>
SQL> --
SQL> -- Peak call vol

SQL> --
SQL> with max_calls as
  2 (select max(call_vol) call_peak
  3 from
  4 (select

  5          to_char(call_start, 'DD-MON-RRRR HH24:MI') start_time,
  6          count(*)  call_vol
  7  from
  8          call_test

  9 group by to_char(call_start, 'DD-MON-RRRR HH24:MI'))  10 )
 11 select start_time, call_vol
 12 from
 13 (select

 14          to_char(call_start, 'DD-MON-RRRR HH24:MI') start_time,
 15          count(*)  call_vol
 16  from
 17          call_test

 18 group by to_char(call_start, 'DD-MON-RRRR HH24:MI'))  19 where call_vol = (select call_peak from max_calls);

START_TIME                CALL_VOL
----------------------- ----------
07-APR-2010 23:40             8886

SQL> David Fitzjarrell Received on Wed Apr 07 2010 - 22:41:45 CDT