220
edits
Changes
Jump to navigation
Jump to search
→Gathering geo data from company addresses
==Gathering geo data from company addresses==
If you do not already have a file with all the geo data in it you can lookup the latitude, longitude data from google using the company address. A link on how to use the [[Geocode.py]] is found here.
==Build dead/alive flags==
First find the deaddate for each company. Make sure that you have companybasecore, ipocore, mascore tables. Then calculate a deaddate. If there is no exit then the deaddate is datelastinv + 5 years. Take a look at the queries below.
CREATE TABLE deaddate AS
SELECT c.coname, c.statecode, c.datefirstinv, c.datelastinv, i.issuedate, m.announceddate
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
CREATE TABLE deaddate1 AS
SELECT *,
CASE
WHEN issuedate IS NULL AND announceddate IS NULL THEN datelastinv + INTERVAL '5 YEAR'
WHEN issuedate IS NOT NULL THEN issuedate
WHEN announceddate IS NOT NULL THEN announceddate
END AS deaddate
FROM deaddate;
--44740
==Creating Stage Flags Table==
