diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 37 |
1 files changed, 19 insertions, 18 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 8c76d5df..76652034 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -51,16 +51,10 @@ CREATE TABLE bonds(id serial primary key, asset_class asset_class, ticket text); - CREATE TABLE position(date date, - isin varchar(12), - cusip varchar(9), - identifier varchar(12), - description varchar(32), + identifier varchar(12) REFERENCES position_indicative(identifier) notional float, - face_amount float, coupon float, - currency currency, factor float, price float, market_value_local float, @@ -71,27 +65,34 @@ CREATE TABLE position(date date, factor_pay_date date, paydown float, writedown float, - bbg_type bbg_type, - strategy bond_strat, - asset_class asset_class, - presettle_principal float, - presettle_interest float, - settle_date date, + unsettled_principal float, + unsettled_interest float, PRIMARY KEY(identifier, date)); +CREATE TABLE position_indicative(identifier varchar(12) PRIMARY KEY, + cusip varchar(9), + isin varchar(12), + description varchar(32), + face_amount float, + currency currency default 'USD', + bbg_type bbg_type default 'Mtge', + asset_class asset_class, + paid_down date default 'Infinity') + CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL) RETURNS TABLE(identifier varchar(12), description varchar(32), strategy bond_strat, - last_settle_date date, notional float) AS $$ + last_settle_date date, notional float, bbg_type bbg_type) AS $$ DECLARE sqlquery text; BEGIN sqlquery := 'WITH temp as (SELECT bonds.identifier, bonds.description, asset_class, settle_date, folder, sum(faceamount*(2*buysell::int-1)) OVER (PARTITION by bonds.identifier) from bonds where trade_date<=$1) - SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, sum AS notional - FROM temp - WHERE (sum>0 or settle_date>=$1)'; + SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, sum AS notional, + position_indicative.bbg_type + FROM temp LEFT JOIN position_indicative USING (identifier) + WHERE (sum>0 or settle_date<=$1) and paid_down>$1 '; IF p_class is not NULL THEN - sqlquery := sqlquery || 'and asset_class=$2'; + sqlquery := sqlquery || 'and position_indicative.asset_class=$2 '; END IF; sqlquery := sqlquery || 'order by identifier, settle_date'; RETURN QUERY EXECUTE sqlquery |