220
edits
Changes
Jump to navigation
Jump to search
→Joining companybasekeys with maskeys and ipokeys
\COPY companybasekeysaddmaskeysaddipokeys TO 'companybasekeysaddmaskeysaddipokeys.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
Open in Excel and add a flag to see the rows with an ipokey as well as a maskey. You can use a formula like this: =IF(OR(ISBLANK(G518),ISBLANK(D518)),0,1). You'll see there are 83 portcokeys that match to an ipokey and a maskey. We'll write a query in sql to take the ipokey or maskey with the lowest date attached to it. This will be the exit date for that portco.
First we create a table that has the minimum exit date. Then we add flags to indicate when the ipokey is valid and when the maskey is valid. Then we create a companybasekeymaskeyipokeycore table that contains clean matches from companybasekey (portcokey) to ipo or mas.
DROP TABLE companybasekeysaddmaskeyaddipokeysmindate;
CREATE TABLE companybasekeysaddmaskeyaddipokeysmindate AS
SELECT *,
CASE
WHEN announceddate IS NOT NULL AND ipoissuedate IS NOT NULL THEN LEAST(announceddate,ipoissuedate)
WHEN announceddate IS NOT NULL THEN announceddate
WHEN ipoissuedate IS NOT NULL THEN ipoissuedate
END AS masterdate
FROM companybasekeysaddmaskeysaddipokeys;
--44740
\COPY companybasekeysaddmaskeyaddipokeyscore TO 'companybasekeysaddmaskeyaddipokeyscore.txt' WITH DELIMITER AS E'\t' HEADER NULL AS ''
CSV
DROP TABLE companybasekeysaddmaskeyaddipokeysmindateflag;
CREATE TABLE companybasekeysaddmaskeyaddipokeysmindateflag AS
SELECT keys.*,
CASE
WHEN announceddate = masterdate THEN 1::int
ELSE 0::int
END AS maskeyvalid,
CASE
WHEN ipoissuedate = masterdate THEN 1::int
ELSE 0::int
END AS ipokeyvalid
FROM companybasekeysaddmaskeyaddipokeysmindate as keys;
--44740
==Name Based Matching companybase keys to ipo keys==
