recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
Is this not flawed in that given these results:
eventtimeevent
-
01-AUG-2001 12:10:00 start add 1- 1
01-AUG-2001 12:15:00 start add 1- 2
01-AUG-2001 12:25:00 start
of Central England
-Original Message-
From: Thomas, Kevin [mailto:[EMAIL PROTECTED]]
Sent: 15 August 2001 16:11
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
Is this not flawed
Subject: RE: An SQL question , not easy ;-)
Or use a self-join
select a.call_start, count(b.call_start) interruptions
from phone_call a, phone_call b
where a.call_start = b.call_start and a.call_end b.call_start
group by a.call_start
-Original Message-
Sent: 15 August 2001 16:02
: Thomas, Kevin [mailto:[EMAIL PROTECTED]]
Sent: 15 August 2001 15:07
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
David,
Here's the output from your suggestion based on a table
with the following rows;
CALL_STARTCALL_END
Yes, and this morning my manager just asked me for something similar
too. How fortunate :0)
g
-Original Message-
Sent: Wednesday, August 15, 2001 7:26 PM
To: Multiple recipients of list ORACLE-L
Hi All:
There's one really neat thing about this thread...it's helped me solve
an
August 2001 16:46
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
Or use a self-join
select a.call_start, count(b.call_start) interruptions
from phone_call a, phone_call b
where a.call_start = b.call_start and a.call_end b.call_start
group
Quick and dirty solution:
Pick a time and look for all calls which started before that time and
ended after that time. That will tell you how many calls were in
progress at that time.
Repeat this at, say, half hour intervals, and graph the results. This
will show you a trend. Where it looks
Pursuing Guy's method in a non-procedural way:
select
ts.timestamp,
count(*)
from
(
select
to_date('1-jan-2001','dd-mon-') +
(rownum / 1440) timestamp
from short_narrow_table_of_numbers
where rownum = 1440
)ts,
phone_calls
where
Thanks Guy !
The problem is that i need it in one non-interactive PL/SQL block .
Thanks a lot !!
-Original Message-
Sent: Wednesday, August 15, 2001 1:00 PM
To: Multiple recipients of list ORACLE-L
Quick and dirty solution:
Pick a time and look for all calls which started before that
How about: -
declare
l_count pls_integer := 0;
begin
for rec in (
select call_start time, 1 incr from table
union all
select call_end time, -1 incr from table
order by 1
) loop
l_count :=
David,
Here's the output from your suggestion based on a table
with the following rows;
CALL_STARTCALL_END
- -
01-AUG-2001 12:10 01-AUG-2001 12:40
01-AUG-2001 12:15 01-AUG-2001 12:30
01-AUG-2001 12:25 01-AUG-2001 12:55
01-AUG-2001 12:45 01-AUG-2001 12:47
Note that whenever a call starts, this increases the current number of calls
by 1, and whenever a call ends, this decreases the current number of calls
by 1. Hence you can uncouple the start and end times - you don't need to
know that a given start time and a given end time belong to the same
I'm working on this one, almost got a solution... ;-)
Kev.
-Original Message-
Sent: 15 August 2001 14:05
To: Multiple recipients of list ORACLE-L
Thanks Guy !
The problem is that i need it in one non-interactive PL/SQL block .
Thanks a lot !!
-Original Message-
Sent:
:[EMAIL PROTECTED]]
Sent: 15 August 2001 15:07
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
David,
Here's the output from your suggestion based on a table
with the following rows;
CALL_STARTCALL_END
PROTECTED]]
Sent: 15 August 2001 15:07
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
David,
Here's the output from your suggestion based on a table
with the following rows;
CALL_STARTCALL_END
- -
01-AUG-2001
Is this not flawed in that given these results:
eventtimeevent
-
01-AUG-2001 12:10:00 start add 1- 1
01-AUG-2001 12:15:00 start add 1- 2
01-AUG-2001 12:25:00 start add 1- total 3
01-AUG-2001 12:30:00 end subtract 1 - 2
: 15 August 2001 15:07
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
David,
Here's the output from your suggestion based on a table
with the following rows;
CALL_STARTCALL_END
- -
01-AUG-2001 12:10 01-AUG
Andrey,
At first glance, I think this SQL statement gives you the answer. It gives you
a count of the simultaneous calls for each call. If you like this, then all you
have to do is find the row with the maximum count.
select a.call_start,a.call_end,count(*)+ 1
from pc a, pc b
where
(l_max_count) );
end;
Regards
David Lord
-Original Message-
From: Thomas, Kevin [mailto:[EMAIL PROTECTED]]
Sent: 15 August 2001 15:07
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
David,
Here's the output from your suggestion based on a table
: Thomas, Kevin [mailto:[EMAIL PROTECTED]]
Sent: 15 August 2001 15:07
To: Multiple recipients of list ORACLE-L
Subject: RE: An SQL question , not easy ;-)
David,
Here's the output from your suggestion based on a table
with the following rows;
CALL_STARTCALL_END
Hi All:
There's one really neat thing about this thread...it's helped me solve an
on-going problem. I've
got managers who want to see graphs of their system's usage. Using this
mechanism applied
against the DBA_AUDIT_TRAIL view works perfectly.
Thanks!
Mike
---
21 matches
Mail list logo
