Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    64

    Question Unanswered: 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

    tia

    a

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Untested aircode but perhaps:

    Code:
     
    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...
    Last edited by pootle flump; 09-28-05 at 08:44. Reason: error counting..
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2004
    Posts
    64
    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

    a

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by arpoodle
    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 )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    Select
    Code:
    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
    groupby
    cr.cli
    having
    count(cr.cli)> 100 andmax(cr.starttime)>dateadd(mi,-15,getdate())
    ) CallIn
    LEFTJOIN callout co 
    ON callin.cli = co.cli 
    where(co.cli isnullordatediff(dy,co.calltime,getdate())>30 ) 

    Here You go ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •