7,612
edits
Changes
Jump to navigation
Jump to search
→Creating portcoexitmaster
==Creating portcoexitmaster==
Portcoexitmaster contains the portcokey with an exitflag, ipoflag and maflag and an exit value. It is built off the companybaseipomasmaster table so be sure you've built this first.Note that the amounts are actually in m (not k) in the input. ALTER TABLE portcoexitbuild RENAME TO portcoexitbuildbak;
CREATE TABLE portcoexitbuild AS
SELECT coname, statecode, datefirstinv, ipoissuedate, masannounceddate, ipoprincipalamtkipoamt, mastransactionamtkmaamt, investedk
FROM companybaseipomasmaster;
--44740
DROP TABLE portcoexitmaster;
CREATE TABLE portcoexitmaster AS
SELECT coname, statecode, datefirstinv, investedk, CASE WHEN ipoissuedate IS NOT NULL THEN ipoissuedate WHEN masannounceddate IS NOT NULL THEN masannounceddate ELSE NULL END AS exitdate,
CASE WHEN ipoissuedate IS NOT NULL THEN 1::int ELSE 0::int END AS ipoflag,
CASE WHEN masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS maflag,
CASE WHEN ipoissuedate IS NOT NULL OR masannounceddate IS NOT NULL THEN 1::int ELSE 0::int END AS exitflag,
CASE WHEN ipoissuedate IS NOT NULL THEN ipoprincipalamtkipoamt::numeric::float8/1000 ELSE mastransactionamtkmaamt::numeric::float8/1000 END AS
exitvaluem
FROM portcoexitbuild;
