help constructing a headache query...

I have two tables, both with phone numbers and call times.

one is for incoming calls, one for outgoing calls.

I need to find all phone numbers from the incoming calls table where the number of calls exceeds 100 within the last 30 days, where the last call was within the last 15 mins, and where the number does Not exist in the outgoing call table within the last 30 days.

so far I have this...
(call record is the incoming, callout is the outgoin)

I believe this is giving me all records in the call record table that are within the last month, and not in the outgoing call table OR have not ben called within the last month..

SELECT cr.cli,min(cr.starttime)as "first call",max(cr.starttime)as "last call",count(cr.cli) as "number of calls"
FROM callrecord cr
LEFT JOIN callout co
ON cr.cli = co.cli
where (co.cli is null or datediff(dy,co.calltime,getdate())>30 )
and datediff(dy,cr.starttime,getdate())>30
group by cr.cli
order by cr.cli

i need to add in the 15 minute call check, and also only return those with a count of > 100

can anyone assist? i'm getting a headache :D

tia

aHi

Untested aircode but perhaps:


SELECT cr.cli,min(cr.starttime)as "first call",max(cr.starttime)as "last call",count(cr.cli) as "number of calls"
FROM callrecord cr
LEFT JOIN (SELECT cli FROM callout WHERE datediff(dy,co.calltime,getdate())>30) co
ON cr.cli = co.cli
where co.cli is null
group by cr.cli
HAVING COUNT(DISTINCT cr.Cli) >100 AND DATEDIFF(n ,MAX(cr.endtime), getdate()) <=15
order by cr.cli

I've assumed there is an end time to the calls that this is when the clock starts ticking...|||there isn't an endtime in these tables.. the call duration isn't relevant, only that a call was made within the last 15 mins, and that the nuimber of calls over the last 30 days have exceeded 100.

that does seem to return a better resultset...

I'll have a look.. thanks :D

a|||there isn't an endtime in these tables.. the call duration isn't relevant, only that a call was made within the last 15 mins, and that the nuimber of calls over the last 30 days have exceeded 100.

Ah - in that case replace the end time with the start time (obviously - reckon you probably figured that :D )|||Select
CallIn.cli
,CallIn.firstcall
,CallIn.lastcall
,CallIn.numberofcalls
from
(
SELECT
cr.cli
,min(cr.starttime)as "firstcall"
,max(cr.starttime)as "lastcall"
,count(cr.cli) as "numberofcalls"
FROM
callrecord cr
where
datediff(dy,cr.starttime,getdate())>30
group by
cr.cli
having
count(cr.cli) > 100 and max(cr.starttime) > dateadd(mi,-15,getdate())
) CallIn
LEFT JOIN callout co
ON callin.cli = co.cli
where (co.cli is null or datediff(dy,co.calltime,getdate())>30 )
Here You go ...