Changes

First flag the undisclosed funds.

CREATE TABLE fundbase1 AS

SELECT *, CASE

FROM fundbase;

--27588

SELECT COUNT(*) FROM fundbase1 WHERE undisclosedflag = 0;

--27097

SELECT COUNT(*) FROM (SELECT DISTINCT fundname, firstinvdate FROM fundbase1 WHERE undisclosedflag = 0)a;

--27097

You can see that fundname, firstinvdate is a good key. But we're going to use simply the fundname as a key because it will be easier to do join operations later.

CREATE TABLE fundbasecore AS

SELECT *

SELECT COUNT(*) FROM (SELECT DISTINCT fundname FROM fundbase1 WHERE undisclosedflag = 0)a;

--27050

The plan is to grab all the duplicate fundnames and only include the ones with the MIN(closedate) AND MIN(lastinvdate) in the fundbasecore table.

DROP TABLE fundnameexclude;

CREATE TABLE fundnameexclude AS