Changes

I then wrote a query to check which whether the IPO issue date or announced date of the MA was lower earlier and used that to indicate whether I chose the company to have undergone an MA or an IPO in the column MSvsIPO(I chose based on which process came first). A 0 in the column represented an MA being chosen and a 1 represented an IPO being chosen. DROP TABLE IPOMASelected; CREATE TABLE IPOMASelected AS SELECT *, CASE WHEN issuedate < announceddate THEN 1 ELSE 0 END AS MAvsIPO FROM IPOMAForReview; --92

SELECT A.coname, A.statecode, A.datefirstinv, B. targetname, B.targetstate, B.announceddate, B.issuername, B.issuerstate, B.issuedate, B.mavsipo 0::int as IPOvsMA FROM IPOMASelected AS B LEFT JOIN companybasecore AS A ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv IPOMAForReview WHERE mavsipoissuedate >=0announceddate;

SELECT A.coname, A.statecode, A.datefirstinv, B.targetname, B.targetstate, B.announceddate, B. issuername, B.issuerstate, B.issuedate, B.mavsipo 1::int as IPOvsMA FROM IPOMASelected AS B LEFT JOIN companybasecore AS A ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv IPOMAForReview WHERE mavsipo=1issuedate < announceddate;

I then put together all of made the IPOs that I selected into one ExitKeysClean table and all of using the MAs I selected into another table. I did this using UNION statements. I did this because I didn't want to duplicate any IPOs portco primary key and UNION acts as a SELECT DISTINCT statement. Thus if the number of the two tables added together equaled the final count of the table, I did not have any duplicate rows. DROP TABLE SelectedIPOSAll; CREATE TABLE SelectedIPOSALL AS SELECT A.coname, A.statecode, A.datefirstinv, A.targetname, A.targetstate, A.announceddate, A.issuername, A.issuerstate, A.issuedate, A.mavsipo FROM ExitKeysCleanIPO AS A UNION SELECT IPOSelectedindicator MAvsIPO indicator column.* FROM IPOSelected; --2116 --Makes sense because 2049+67=2116

DROP TABLE SelectedMASAllExitKeys; CREATE TABLE SelectedMASALL ExitKeys AS SELECT A.coname, A.statecode, A.datefirstinv, A.targetname, A.targetstate, A.announceddate, A.issuername, A.issuerstate, A.issuedate, A.mavsipo ipovsma FROM ExitKeysCleanMA AS AIPONoConflict UNION SELECT MASelected.* FROM MASelected; --7121 --Makes sense because 25+7096=7121 I then checked both of these files to make sure that their primary keys were still distinct and thus valid. SELECT COUNT(*) FROM(SELECT DISTINCT targetnameconame, targetstatestatecode, announceddate FROM SelectedMasAll)a; --7121  SELECT COUNT(*) FROM(SELECT targetname, targetstatedatefirstinv, announceddate ipovsma FROM SelectedMasAll)a; --7121IPOSelected UNION SELECT COUNT(*) FROM(SELECT issuernameconame, issuerstatestatecode, issuedate FROM SelectedIPOMA)a; --2116  SELECT COUNT(*) FROM(SELECT DISTINCT issuernamedatefirstinv, issuerstate, issuedate ipovsma FROM SelectedIPOMA)a; --2116 I combined the two tables and checked the result to make sure it Unioned correctly.  DROP TABLE SelectedIPOMA; CREATE TABLE SelectedIPOMA AS SELECT A.* FROM SelectedMASALL AS AMANoConflict UNION SELECT B.* FROM SelectedIPOSALL AS B; --9237  SELECT COUNT(*) FROM SelectedIPOMA WHERE mavsipo=0; --7121  SELECT COUNT(*) FROM SelectedIPOMA WHERE mavsipo=1; --2116  SELECT COUNT(*) FROM SelectedIPOMA WHERE targetname IS NOT NULL; --7188 SELECT COUNT(*) FROM SelectedIPOMA WHERE issuername IS NOT NULL; --2141 Everything seems to check out, so we can move on to joining the SelectedIPOMA table to the companybasecore table to create the ExitKeysClean table. DROP TABLE ExitKeysClean; CREATE TABLE ExitKeysClean AS SELECT A.coname, A.statecode, A.datefirstinv, B.targetname, B.targetstate, B.announceddate, B.issuername, B.issuerstate, B.issuedate, B.mavsipo ipovsma FROM companybasecore AS A LEFT JOIN SelectedIPOMA AS B ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinvMASelected; --48000 Since 48000 is the same number of rows in the companybasecore, we know that the join was successful and no rows were added that we don't want. POTENTIAL CAUSE OF ERROR: I am missing 5 entries from the SelectedIPOMA WHERE mavsipo=0. The answer should be 7121 but instead it is 7116. Not sure why this is happening. UPDATE: I have found the 5 missing entires but am unsure as to why thy are not being included in the ExitKeysClean table. coname | statecode | datefirstinv | targetname | targetstate | announceddate | issuername | issuerstate | issuedate | mavsipo | equivalence --------------------+-----------+--------------+-------------------+-------------+---------------+------------+-------------+-----------+---------+------------- Corbel & Company | FL | 1985-04-01 | Corbel & Co Inc | FL | 1993-03-29 | | | | 0 | 0 Deltak Corporation | IL | 1971-08-01 | Deltak Corp | MN | 1993-12-28 | | | | 0 | 0 Wine.com | CA | 1995-07-01 | Wine.com | CA | 2001-04-27 | | | | 0 | 1 CHF Solutions Inc | MN | 1999-06-30 | CHF Solutions Inc | MN | 2010-01-20 | | | | 0 | 1 Packet Design Inc | CA | 2000-06-13 | Packet Design LLC | CA | 2013-03-19 | | | | 0 | 09215