Search for MOST RECENT duplicate unanswered telephone number called...

Posted on 2009-12-25
Last Modified: 2013-12-12
I have millions of outbound phone call dialing attempts made by customers.  All are in chronological order ("order by calIdate_est", which is Eastern Standard Time.  

I want to know if any expert out there (including simon3270) can produce a Linux command-line-based script or a MySQL SELECT statement that will return only the most recent record for each phone number called  that has 0 billable seconds.  In other words, I need to know the last time a number was called and was not answered.

Each record has:
calldate_est (a date and time stamp);
dst (the number called, except when the call fails due to congestion or the number is invalid or unassigned, in which case is found in the userfield's first 10 digits);
userfield (see prior explanation); and
billsec (number of seconds after call has been answered

Let's say 212-NNN-1234 was called 10 times over the past three months.  The 10 attempts looked like this:

2009-09-12 16:00:17,212-NNN-1234,ANSWERED,31
2009-09-15 12:06:03,212-NNN-1234,ANSWERED,71
2009-09-16 13:11:01,212-NNN-1234,ANSWERED,22
2009-09-17 09:11:44,212-NNN-1234,ANSWERED,15
2009-09-19 10:00:17,212-NNN-1234,MACHINE,6
2009-09-21 13:19:16,212-NNN-1234,ANSWERED,19
2009-09-22 17:03:22,212-NNN-1234,ANSWERED,3
2009-09-25 18:09:45,212-NNN-1234,MACHINE,6
2009-09-27 19:09:52,212-NNN-1234,ANSWERED,4

I need a script (or C language app) that will parse through a flat file of output from a MySQL SELECT, or a SELECT used directly to pull out only the most recent unanswered call for each number (where billsec = 0).  I also need to be able to alter the target number of seconds from 0 through, let's say 60.  A command line variable would be perfect.
Question by:jerry_gilels
    LVL 1

    Accepted Solution

    Here the asswer in SQL ..I hope this can be used if you can switch your solution to SQL from C
    SELECT dst, MAX(call_est) AS call_est
    WHERE billsec = 0
    GROUP BY dst
    ORDER BY dst

    Author Comment

    SELECT dst, MAX(call_est) AS call_est
    WHERE billsec = 0
    GROUP BY dst
    ORDER BY dst

    returns this:

    Error Code : 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE billsec = 0
    GROUP BY dst
    ORDER BY dst' at line 2
    (0 ms taken)
    LVL 11

    Assisted Solution

    You're missing the FROM statement:

    SELECT dst, MAX(call_est) AS call_est
    FROM tablename
    WHERE billsec = 0
    GROUP BY dst
    ORDER BY dst

    Open in new window

    LVL 107

    Expert Comment

    by:Ray Paseur
    You may also need parentheses around the GROUP BY column - not sure, but I seem to recall this from other GROUP BY queries I have done.

    Also, I do not really understand your description, "the number called, except when the call fails..." so you might need some additional discriminator in the WHERE clause.  Just add "AND" after billsec = 0 and before GROUP to insert an additional discriminator.

    HTH, ~Ray

    Author Closing Comment

    NetCitizen got the gist of the solution.  Although he forgot the FROM statement, asafadis's net caught the omission.

    So, each answer deserves to receive points.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now