158
edits
Changes
Jump to navigation
Jump to search
→Create the PortCoExit And PortCoAliveDead Tables
CREATE TABLE PortCoExit AS
SELECT A.coname, A.statecode, A.datefirstinv, A.datelastinv, A.city,
CASE WHEN B.ipovsma IS NOT NULL THEN 1::int ELSE 0::int END AS Exit, ipovsma, CASE WHEN B.ipovsma=1 THEN C.proceedsamt::numeric WHEN ipovsma=0 THEN D.transactionamt::numeric ELSE NULL::numeric END AS exitvaluem, CASE WHEN B.ipovsma=1 THEN C.issuedate WHEN ipovsma=0 THEN effectivedate D.announceddate ELSE NULL::date END AS exitdate, CASE WHEN B.ipovsma=1 THEN extract(year from C.issuedate) WHEN ipovsma=0 THEN extract(year from effectivedateD.announceddate) ELSE NULL::int END AS exityear
FROM companybasecore AS A
LEFT JOIN ExitKeys AS B ON A.coname=B.coname AND A.statecode=B.statecode AND A.datefirstinv=B.datefirstinv
