Re: max concurrent record query

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 22 Jun 1999 17:48:32 +0100
Message-ID: <930070957.19561.0.nnrp-06.9e984b29@news.demon.co.uk>

You could convert your procedural strategy into the equivalent non-procedural one by creating a table of 120 rows, one for each 5 minute interval in a day

create table time_list(interval_start date).

insert into time_list values(

    to_date('21-jun-1999 00:00:00','dd-mon-yyyy hh24:mi:ss') );

You would probably populate this table in a less arduous way than inserting 120 rows in the above style.

Combine this with your two log tables:

select

    tl.interval_start,count(*)
from

    time_list tl,
    (

        select
            lin.login_time,
            nvl(lout.logout_time,to_date('31-dec-2999','dd-mon-yyyy')
logout_time
        from
            login_times    lin,
            logout_times    lout
        where
            lout.id (+) = lin.id

 ) lt
 where

         lt.login_time <=tl.interval_start and lt.logout_time >lt.interval_start group by

    lt.interval_start
;

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Scott Florcsk wrote in message <7ko9df$bg3$1_at_nnrp1.deja.com>...
>I was having a problem I was hoping some SQL Wizard could help with:
>We log our dial-up access statistics into an Oracle database, and then
>I write a couple of reports on them. There are 2 tables: a
>start_session and an end_session. Each table has a time and a unique
>session ID that is assigned by the modem server, so because each
>occurrance of logging on and logging off gets it's own log record, it
>was easier to put the records into 2 tables and then join them with the
>unique modem server-generated ID. Anyways, here's the gist of the
>problem: one of the requests that came in is to know what the maximum
>number of users that were logged in at any one time, and how many were
>there.
>
>I was thinking I could do it in a procedure that loops through a day
>(starting at midnight) at 5 minute intervals and does a count on number
>of records where loop_time between start_time and end_time, and
>increment a counter by 1, and store the max in some variable. But my
>senses tell me that this can't be the best way of doing this, because
>it doesn't seem clean to me at all.
>
>I'd rather do it in straight SQL and not PL/SQL
Received on Tue Jun 22 1999 - 11:48:32 CDT