158
edits
Changes
Jump to navigation
Jump to search
→Process For Creating the PortCoExits Table
==Process For Creating the PortCoExits Table==
Even if you manually checked the excel sheet for Warning Multiple Matches with the Hall warning, there still may be duplicates. Check this by running:
SELECT COUNT(*) FROM(SELECT targetname, targetstate, announceddate FROM MAClean)a;
--7206
SELECT COUNT(*) FROM(SELECT DISTINCT targetname, targetstate, announceddate FROM MAClean)a;
--7189
SELECT COUNT(*) FROM(SELECT issuername, issuerstate, issuedate FROM IPOClean)a;
--2146
SELECT COUNT(*) FROM(SELECT DISTINCT issuername, issuerstate, issuedate FROM IPOClean)a;
--2141
As you can see, I still have duplicates in both the MAClean and IPOClean files. I ran an aggregate function to get rid of these duplicates:
DROP TABLE MACleanNoDups;
CREATE TABLE MACleanNoDups AS
SELECT targetname, targetstate, announceddate, MIN(x1) as x1, MIN(x2) as x2, MIN(method) as method, MIN(conamestd) as conamestd, MIN(coname) as coname, MIN(statecode) as
statecode, MIN(datefirstinv) as datefirstinv, MIN(targetnamestd) as targetnamestd
FROM MAClean
GROUP BY targetname, targetstate, announceddate;
DROP TABLE IPOCleanNoDups;
CREATE TABLE IPOCleanNoDups AS
SELECT issuername, issuerstate, issuedate, MIN(x1) as x1, MIN(x2) as x2, MIN(method) as method, MIN(conamestd) as conamestd, MIN(coname) as coname, MIN(statecode) as statecode,
MIN(datefirstinv) as datefirstinv, MIN(issuernamestd) as issuernamestd
FROM IPOClean
GROUP BY issuername, issuerstate, issuedate;
--2141
Now the duplicates are out of the MAClean and IPOClean data and we can start to construct the PortCoExit table.
First I looked for the PortCos that were in both the MAs and the IPOs. I did this using:
DROP TABLE IPOMAForReview;
