7,649
edits
Changes
Jump to navigation
Jump to search
VC Database Rebuild (view source)
Revision as of 11:35, 27 September 2017
, 11:35, 27 September 2017→Redoing the companybase with the new SDC company data
--22374
\COPY roundleveloutput2 TO 'roundleveloutput2.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
===Creating a PortCoMaster Table===
Using '''sdccompanybasecore2''' as a base table, Ed created a useful PortCoMasterTable. This table isn't perfect:
*sdccompanybasecore2 was drawn later and has 44966. It has fixed addresses (without copy down mistakes)
*The other tables - PortCoDeadAliveMaster, PortCoExitMaster, and PortCoFlagMaster - are built off slightly earlier draws and so have 44740 records
-The other tables are all keyed by coname, statecode and datefirstinv.
DROP TABLE PortCoMaster;
CREATE TABLE PortCoMaster AS
SELECT A.*,
B.deaddate, B.aliveyear, B.deadyear,
C.ipoflag, C.maflag, C.exitflag, C.exitvaluem,
D.hadgrowth, D.numgrowth, D.hadseed, D.numseed, D.hadearly, D.numearly, D.hadlater, D.numlater, D.hadtrans,
D.numtrans, D.hadexcl, D.numexcl, D.haddeal, D.numrounds, D.totalinvestors
FROM sdccompanybasecore2 AS A
LEFT JOIN PortCoDeadAliveMaster AS B ON A.coname=B.coname AND A.statecode=B.statecode AND
A.datefirstinv=B.datefirstinv
LEFT JOIN PortCoExitMaster AS C ON A.coname=C.coname AND A.statecode=C.statecode AND A.datefirstinv=C.datefirstinv
LEFT JOIN PortCoFlagMaster AS D ON A.coname=D.coname AND A.statecode=D.statecode AND A.datefirstinv=D.datefirstinv;
--44966
\COPY PortCoMaster TO 'PortCoMaster.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
--44966
==Cleaning roundline==
