1 files changed, 12 insertions, 8 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index a08ae6c7..551b4b55 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -2823,15 +2823,19 @@ CREATE TABLE public.quantifi_bond_proxy (
 
 
 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)
+ RETURNS TABLE(index text, tradeid text, fund fund, clean_folder text, portfolio portfolio, security_id varchar(12), 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$
+	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
+FROM (
+SELECT cds.fund, account_code AS fcm, cds.security_id, max(security_desc) AS security_desc, maturity, folder,
+SUM(cds.notional * (CASE WHEN protection='Buyer' 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 a.folder::text=pfm.clean_folder
+LEFT JOIN index_maturity_markit imm ON (a.SECURITY_id, a.maturity) = (imm.redindexcode, imm.maturity) WHERE active AND a.notional !=0;
+end;
+$function$
 ;