Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-12-25
5
Medium Priority
?
281 Views
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.
0
Comment
Question by:jerry_gilels
5 Comments
 
LVL 1

Accepted Solution

by:
NetCitizen earned 1000 total points
ID: 26123226
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
 

Author Comment

by:jerry_gilels
ID: 26123334
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
 
LVL 11

Assisted Solution

by:asafadis
asafadis earned 1000 total points
ID: 26123406
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26124295
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
 

Author Closing Comment

by:jerry_gilels
ID: 31669965
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month21 days, 2 hours left to enroll

810 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