asked on

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

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.

ASKER CERTIFIED SOLUTION

NetCitizen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jerry_gilels

ASKER

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)

SOLUTION

asafadis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

jerry_gilels

ASKER

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.