diff options
| -rw-r--r-- | sql/dawn.sql | 14 |
1 files changed, 7 insertions, 7 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index cecfbd02..4da8bbf8 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3432,19 +3432,19 @@ CREATE TABLE quantifi_bond_proxy ( CREATE OR REPLACE FUNCTION list_compressed_quantifi(start_from date) - RETURNS TABLE(index text, tradeid text, fund fund, clean_folder text, portfolio portfolio, security_id varchar(12), notional double precision, fcm text) + RETURNS TABLE(index text, tradeid text, fund fund, clean_folder text, portfolio portfolio, security_id character varying, notional double precision, fcm text, maturity date) LANGUAGE plpgsql AS $function$ begin return query - SELECT LEFT(split_part(security_desc, ' ', 2),2) AS "index", imm.tradeid, a.fund, pfm.clean_folder, pfm.portfolio, a.security_id, a.notional, a.fcm + SELECT LEFT(split_part(security_desc, ' ', 2),2) AS "index", imm.tradeid, a.fund, pfm.clean_folder, pfm.portfolio, a.security_id, a.notional, a.fcm, a.maturity FROM ( -SELECT cds.fund, account_code AS fcm, cds.security_id, max(security_desc) AS security_desc, maturity, folder, +SELECT cds.fund, account_code AS fcm, cds.security_id, max(security_desc) AS security_desc, cds.maturity, folder, SUM(cds.notional * (CASE WHEN protection='Buy' THEN 1 ELSE -1 END)) AS notional FROM cds -WHERE swap_type='CD_INDEX' AND trade_date<=start_from AND maturity > start_from -GROUP BY cds.security_id, maturity, cds.folder, cds.fund, account_code) a -LEFT JOIN portfolio_folder_mapping pfm ON replace(a.folder::text, 'SER_', '')=pfm.clean_folder -LEFT JOIN index_maturity_markit imm ON (a.SECURITY_id, a.maturity) = (imm.redindexcode, imm.maturity) WHERE active AND abs(a.notional) >1; +WHERE swap_type='CD_INDEX' AND trade_date<=start_from AND cds.maturity > start_from +GROUP BY cds.security_id, cds.maturity, cds.folder, cds.fund, account_code) a +LEFT JOIN portfolio_folder_mapping pfm ON REPLACE(a.folder::TEXT, 'SER_', '')=pfm.clean_folder +LEFT JOIN index_maturity_markit imm ON (a.SECURITY_id, a.maturity) = (imm.redindexcode, imm.maturity) WHERE active AND abs(a.notional) > 1; end; $function$ ; |