diff options
| -rw-r--r-- | sql/dawn.sql | 27 |
1 files changed, 27 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 6cec10ff..7fbb8cbd 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -5016,3 +5016,30 @@ SELECT fund, folder::text, termination_id from trs_termination_mapping) a LEFT J terminations on a.termination_id = terminations.id; +CREATE OR REPLACE FUNCTION compare_citco_bonds( + p_date date, + p_fund fund +) RETURNS TABLE ( + citco_security_id text, + security_id text, + serenitas_factor float, + serenitas_notional float, + admin_factor float, + admin_notional float +) +AS $$ +BEGIN + RETURN QUERY + SELECT b.citco_security_id::text, a.identifier::text AS security_id, a.factor AS serenitas_factor, + a.notional AS serenitas_notional, b.factor AS admin_factor, + b."quantity(end)" AS admin_notional + FROM risk_positions(p_date, NULL, p_fund, True) a + LEFT JOIN + ( + SELECT cusip, factor, "quantity(end)", cr.citco_security_id + FROM citco_reports cr + WHERE fund=p_fund AND cusip IS NOT NULL AND period_end_date=p_date + ) b + ON a.identifier=b.cusip; +END; +$$ LANGUAGE PLPGSQL;
\ No newline at end of file |