diff options
| -rw-r--r-- | sql/dawn.sql | 17 |
1 files changed, 16 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 1b612c44..a08ae6c7 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2819,4 +2819,19 @@ CREATE TABLE public.quantifi_bond_proxy ( notional float8 NULL, terminated bool NULL DEFAULT false, CONSTRAINT quantifi_bond_proxy_pk PRIMARY KEY (product_name, trade_date, asset_class, fund) -);
\ No newline at end of file +); + + +CREATE OR REPLACE FUNCTION public.list_compressed_quantifi(start_from date) + RETURNS TABLE(index text, tradeid text, fund fund, clean_folder text, portfolio portfolio, security_id text, notional double precision, fcm text) + LANGUAGE plpgsql +AS $function$ +begin + return query + SELECT LEFT(split_part(security_desc, ' ', 2),2) AS "index", imm.tradeid, a.fund::fund, pfm.clean_folder, pfm.portfolio, a.security_id::text, a.notional, a.fcm +FROM (SELECT *, 'SERCGMAST' AS "fund", 'BAML' AS "fcm" FROM list_cds_positions_by_strat_fcm(start_from, 'BAML','SERCGMAST') +UNION SELECT *, 'SERCGMAST' AS "fund", 'WF' AS "fcm" FROM list_cds_positions_by_strat_fcm(start_from, 'WF','SERCGMAST') UNION SELECT *, 'BOWDST' AS "fund", 'GS' AS "fcm" FROM list_cds_positions_by_strat_fcm(start_from, 'GS', 'BOWDST')) a +LEFT JOIN portfolio_folder_mapping pfm ON a.folder::text=pfm.clean_folder +LEFT JOIN index_maturity_markit imm ON (a.SECURITY_id, a.maturity) = (imm.redindexcode, imm.maturity) WHERE active; +end;$function$ +; |