Fixing lead table with duplicate leads

[jwhawkins]

OK... I've explained to my client the importance of using the duplicate check feature of the list loader, but inevitably, they failed to use it...

So, the question is, is there a /simple/ method to deobfuscate this situation? There are duplicate leads in the lead list - however, the complicating factor is that all the duplicate leads have different lead_id's (I assume this is used as the primary key on the table).

At the point at which it was brought up to me, there are currently 54,000+ leads in the table, a large chunk of which are duplicates. I've tried using a variety of methods to fix this, including:

SELECT DISTINCT (and various permutations thereof) SQL statements
sed/awk scripting (the major result of which was the intake of mass quantities of alcohol)
grep & regexing (see item #2 for result)

This situation has me utterly perplexed, and I'm afraid that my PHP ability falls far short of being able to fix this issue. My coding ability, as a whole, falls short of doing this outside of COBOL.

Any ideas, anyone ?

[xirin6]

Hello my name is Kevin I work with Matt F. My question is have these leads in this list been dialed on yet?

Kevin Kelley

[henry]

I have experienced this problem many times before (sometimes I've screwed the data up, and sometimes other people!) and the best way to fix it is with a simple script customised to suit your situation.

Basically you can use a query like this to find the duplicates:

SELECT phone_number, COUNT(*) AS num FROM vicidial_list GROUP BY phone_number HAVING num>1

You can then iterate over the result set in your PHP script (or anything else that you can connect to the database with) and delete one of the records. Usually I would delete the record which has not been called yet or has already been closed with a sale or not interested etc.

An alternative to deleting the record is to flag is as something else so that it is not called by changing the status or some other field for a different database structure.

[jwhawkins]

Some have been called, some have not. I will try that query and see if it gives me any results

-- Jake Hawkins