diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 42 |
1 files changed, 25 insertions, 17 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 935f666b..8c255827 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -526,7 +526,8 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL, - include_unsettled boolean DEFAULT True) + include_unsettled boolean DEFAULT True, + p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat, curr_cpn float, start_accrued_date date, last_settle_date date, principal_payment float, accrued_payment float, currency currency, daycount day_count, @@ -548,14 +549,15 @@ BEGIN END IF; sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder, principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) - OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date<=$1) + OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date<=$1 + AND fund=$3) SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, securities.coupon, start_accrued_date, settle_date, temp.principal_payment, temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type FROM temp LEFT JOIN securities USING (identifier) WHERE (temp.notional>0 '||unsettled_opt||') AND paid_down>$1 '||asset_opt ||' ORDER BY identifier, settle_date desc'; - RETURN QUERY EXECUTE sqlquery USING p_date, p_class; + RETURN QUERY EXECUTE sqlquery USING p_date, p_class, p_fund; END; $$ LANGUAGE plpgsql; @@ -586,7 +588,9 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE +CREATE OR REPLACE function risk_positions(p_date date, + p_assetclass asset_class, + p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE (description varchar(32), identifier varchar(12), notional float, price float, strategy bond_strat, factor float, local_market_value float, usd_market_value float, curr_cpn float, int_acc float, last_pay_date date, principal_payment float, @@ -600,7 +604,7 @@ BEGIN a.notional * coalesce(b.factor,1) * fxrate * yearfrac(case WHEN start_accrued_date>=p_date+1 THEN b.prev_cpn_date ELSE start_accrued_date END, p_date+1, daycount) * b.coupon/100., b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date - FROM list_positions(p_date, p_assetclass) a + FROM list_positions(p_date, p_assetclass, true, p_fund) a LEFT JOIN factors_history(p_date) b USING (identifier) LEFT JOIN list_marks(p_date, True) c USING (identifier) LEFT JOIN fx_rate(p_date) USING (currency) @@ -630,7 +634,8 @@ BEGIN query := 'WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, cds.currency, %s 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) AS notional -FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date> $1) and cds.trade_date <=$1 %s) +FROM cds WHERE cds.fund=$2 AND (cds.termination_date is NULL OR cds.termination_date> $1) + AND cds.trade_date <=$1 %s) SELECT DISTINCT ON (tmp.security_id, tmp.maturity %s) * FROM tmp WHERE tmp.notional!=0'; IF p_type = 'tranche' THEN @@ -647,23 +652,26 @@ END IF; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_cds_positions (p_date date, strat cds_strat DEFAULT NULL::cds_strat) +CREATE OR REPLACE function list_cds_positions (p_date date, + strat cds_strat DEFAULT NULL::cds_strat, + 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 $$ BEGIN IF strat IS NULL THEN - RETURN QUERY EXECUTE query_positions('cds') USING p_date; + RETURN QUERY EXECUTE query_positions('cds') USING p_date, fund; ELSE RETURN QUERY SELECT a.security_id, a.security_desc, a.maturity, a.fixed_rate, a.currency, a.notional - FROM list_cds_positions_by_strat(p_date) a + FROM list_cds_positions_by_strat(p_date, fund) a WHERE folder=strat; END IF; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_cds_positions_by_strat(p_date date) +CREATE OR REPLACE function list_cds_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, folder cds_strat, notional float) AS $$ BEGIN @@ -673,7 +681,7 @@ RETURN QUERY SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, 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.attach is NULL AND cds.folder != 'MBSCDS') + cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS' AND cds.fund=p_fund) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; END; $$ LANGUAGE plpgsql; @@ -685,12 +693,12 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, BEGIN RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.fixed_rate, - cds.currency, cds.folder + cds.currency, cds.folder, SUM(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END)) OVER (PARTITION BY cds.security_id, cds.maturity, cds.folder, cds.account_code) AS notional FROM cds WHERE (cds.termination_date is NULL OR cds.termination_date > p_date) AND cds.trade_date <= p_date AND cds.attach is NULL AND cds.folder != 'MBSCDS' - AND cds.account_code=fcm::text) + AND cds.account_code=fcm) SELECT DISTINCT ON (tmp.security_id, tmp.maturity, tmp.folder) * FROM tmp WHERE tmp.notional!=0; END; $$ LANGUAGE plpgsql; @@ -714,22 +722,22 @@ RETURN result; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_tranche_positions(p_date date) +CREATE OR REPLACE function list_tranche_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, orig_attach smallint, orig_detach smallint, attach float, detach float, initial_margin_percentage float, notional float) AS $$ BEGIN -RETURN QUERY EXECUTE query_positions('tranche') USING p_date; +RETURN QUERY EXECUTE query_positions('tranche') USING p_date, fund; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE function list_abscds_positions(p_date date) +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 $$ BEGIN -RETURN QUERY EXECUTE query_positions('abs') USING p_date; +RETURN QUERY EXECUTE query_positions('abs') USING p_date, fund; END; $$ LANGUAGE plpgsql; |