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.
jerry_gilelsAsked:
Who is Participating?
 
NetCitizenCommented:
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
0
 
jerry_gilelsAuthor Commented:
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)
0
 
asafadisCommented:
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

0
 
Ray PaseurCommented:
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
0
 
jerry_gilelsAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.