DROP TABLE MACleanNoDups;
CREATE TABLE MACleanNoDups AS
SELECT A.*, effectivedate, transactionamt, enterpriseval, acquirorstatustacquirorstatus
FROM MAClean AS A
JOIN (
) AS B
ON A.targetname=B.targetname AND A.targetstate=B.targetstate AND A.announceddate=B.announceddate
LEFT JOIN MANoDups AS C ON A.targetnametargetnamestd=C.targetname AND A.targetstate=C.targetstate AND A.announceddate=C.announceddate; --7171
SELECT COUNT(*) FROM(SELECT DISTINCT coname, statecode, datefirstinv FROM MACleanNoDups)a;