diff options
| -rw-r--r-- | sql/dawn.sql | 24 |
1 files changed, 13 insertions, 11 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 579a0fee..ae6460d4 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -90,14 +90,16 @@ CREATE TABLE payment_history( principal_paydown float, interest float, principal_writedown float, - recovery float, + recovery_percentage float, + cumloss_percentage float, + PRIMARY KEY (identifier, date)); -CREATE OR REPLACE function list_marks(p_date date) +CREATE OR REPLACE function list_marks(p_date date, include_unsettled boolean DEFAULT False) RETURNS TABLE(identifier varchar(12), price float) AS $$ BEGIN - RETURN QUERY SELECT a.identifier, b.price FROM list_positions(p_date) a LEFT JOIN -(select distinct on (identifier) date, marks.identifier, marks.price FROM marks -WHERE date<= p_date ORDER BY identifier, date DESC) b USING (Identifier); + RETURN QUERY SELECT a.identifier, b.price FROM list_positions(p_date, Null, include_unsettled) a LEFT JOIN +(SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks +WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); END; $$ LANGUAGE plpgsql; @@ -123,12 +125,12 @@ BEGIN END IF; 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, - position_indicative.bbg_type - FROM temp LEFT JOIN position_indicative USING (identifier) - WHERE (sum>0 '||unsettled_opt||') and paid_down>$1 '||asset_opt - ||' order by identifier, settle_date'; + OVER (PARTITION by bonds.identifier) notional from bonds where trade_date<=$1) + SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, notional, + 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; END; $$ LANGUAGE plpgsql; |