diff options
| -rw-r--r-- | sql/dawn.sql | 20 |
1 files changed, 20 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 8c255827..1743716a 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -733,6 +733,26 @@ RETURN QUERY EXECUTE query_positions('tranche') USING p_date, fund; END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_tranche_positions_by_strat(p_date date, + p_fund fund DEFAULT 'SERCGMAST'::fund) +RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, + fixed_rate float, currency currency, orig_attach smallint, + orig_detach smallint, attach float, detach float, + folder cds_strat, notional float) AS $$ +BEGIN +RETURN QUERY + WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, + cds.currency, cds.orig_attach, cds.orig_detach, + cds.attach, cds.detach, cds.folder, + SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) + OVER (PARTITION BY cds.security_id, cds.maturity, cds.orig_attach, cds.orig_detach, cds.folder) AS notional + FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND + cds.trade_date <= p_date AND cds.orig_attach is NOT NULL AND cds.fund=p_fund) + SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.orig_attach, tmp.folder) * FROM tmp WHERE tmp.notional!=0; +END; +$$ LANGUAGE plpgsql; + + CREATE OR REPLACE function list_abscds_positions(p_date date, fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, fixed_rate float, currency currency, notional float) AS $$ |