Search for MOST RECENT duplicate unanswered telephone number called...
Posted on 2009-12-25
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:
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.