Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can I do this with a single statement?

Status
Not open for further replies.

vvhitekid2

Technical User
Jul 8, 2003
56
US
This should be an easy question for a seasoned Oracle person:

I have a system setup to record info about calls coming into our company. My simple table has 3 fields:
DATESTAMP (date)
ORIG (varchar) <- caller id of person calling
DEST (varchar) <- Internal ext person called


SELECT COUNT(1) FROM calldata WHERE (dest = ?) AND (DATESTAMP < sysdate - 1) AND (DATESTAMP > sysdate-8)")

This tells me how many calls an employee got last week. Now I need to modify this somehow to give the number of "unresolved" callers (how many people that talked to this person called ANYONE in the compay back within 24 hours after talking to employee?)

Is there a way to do this with just SQL, or will I have to setup arrays to store values? I know how to do it with loops and a few arrays, but it seems very inefficient. Can Oracle do better than a few nested for loops?

Thanks in advnace!
 
Hi,
First of all tell us that how do you decide if a call is resolved or not.
Is there any Status field.
If there is no Status feild then how do you decide if any nth call is resolved or not.

Regards
Himanshu
 
Hi, thanks for your help. A call is resolved if the same caller id does not show up in the table within 24 hours of the original call.

An "unresolved" call would be counted for that paticular employee if the same phone number called back within 24 of the original call.

Make sense?
 
Code:
select count (*) from
(
select
  cd.datestamp
, cd.orig
, cd.dest
, (select count(*) from calldata 
   where orig = cd.orig
      and datestamp > cd.datestamp
      and datestamp < cd.datestamp+1) no_of_repeating_calls
)
where 
    dest = ? 
and datestamp < sysdate - 1
and datestamp > sysdate-8
and no_of_repeating_calls>0
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top