Can I do this with a single statement?

[vvhitekid2]

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!

 

[HimanB]

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

 

[vvhitekid2]

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?

 

[nagornyi]

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