220
edits
Changes
Jump to navigation
Jump to search
→Creating portcotransactionmaster table
==Creating portcotransactionmaster table==
Before doing this stage make sure you have followed the steps in [[#Joining companybasekeys with maskeys and ipokeys|Joining companybasekeys with maskeys and ipokeys]]
You will be joining the companybasecore table with the mascore and ipocore through the companybasekeyipokey and companybasekeymaskey tables. The output master table will have each company name and the dates and amounts if they received a ipo or ma. As discussed in [[#Joining companybasekeys with maskeys and ipokeys|Joining companybasekeys with maskeys and ipokeys]] the master table includes the exit deal which had the minimum date so duplicate rows should not crop up in the master table.
DROP TABLE companybaseipomasmaster;
CREATE TABLE companybaseipomasmaster AS
SELECT c.coname, c.statecode, c.datefirstinv, c.investedk, c.city, c.addr1, c.addr2, ipokey.ipoissuedate, maskey.announceddate AS
masannounceddate, i.principalamt AS ipoprincipalamtk, m.transactionamt AS mastransactionamtk
FROM companybasecore AS c
LEFT JOIN companybasekeyipokeycore AS ipokey ON c.coname = ipokey.coname AND c.statecode = ipokey.statecode AND c.datefirstinv =
ipokey.datefirstinv
LEFT JOIN companybasekeymaskeycore AS maskey ON c.coname = maskey.coname AND c.statecode = maskey.statecode AND c.datefirstinv =
maskey.datefirstinv
LEFT JOIN ipocore AS i ON i.issuer = ipokey.ipoissuer AND i.issuedate = ipokey.ipoissuedate AND i.statecode = ipokey.ipostatecode
LEFT JOIN mascore AS m ON m.targetname = maskey.mastargetname AND m.targetstatecode = maskey.masstatecode AND m.announceddate =
maskey.announceddate;
--44740
\COPY companybaseipomasmaster TO 'companybaseipomasmaster.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
==Name Based Matching companybase keys to ipo keys==
