diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 50 |
1 files changed, 25 insertions, 25 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 12a13a4c..eb55b574 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2283,36 +2283,36 @@ END $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_crt_data(p_date date, fund fund DEFAULT 'SERCGMAST'::fund) -RETURNS TABLE(identifier varchar(9), model_time timestamp, notional double precision, factor double precision, pv float, duration float4, dm float4, delta_ir float4, delta_economy float4, +RETURNS TABLE(identifier varchar(9), description varchar, model_time timestamp, notional double precision, factor double precision, pv float, duration float4, dm float4, delta_ir float4, delta_economy float4, convexity_ir float4, convexity_economy float4, bondtype crt_bond_type, delta numeric) as $$ BEGIN RETURN QUERY --- SELECT DISTINCT ON (identifier) c.identifier, a.timestamp, c.notional, c.factor, a.pv, duration_fw, a.dm, "delta.ir", "delta.economy", --- "convexity.ir", "convexity.economy", bond_type, value FROM risk_positions(p_date, 'CRT', fund) c --- LEFT JOIN priced_at_market a on c.identifier = a.cusip --- JOIN crt_model_versions ON model_des=model_version --- LEFT JOIN map_cusip USING (cusip) --- LEFT JOIN bond_types USING (bond) --- LEFT JOIN beta_estimates USING (bond_type) --- WHERE until_date > p_date --- AND timestamp BETWEEN p_date AND p_date + INTERVAL '1 DAY' --- AND date <= greatest(p_date, DATE '2021-02-01') --- ORDER BY identifier, until_date ASC, timestamp DESC, date DESC; - SELECT c.identifier, a.timestamp, c.notional, c.factor, a.pv, duration_fw, a.dm, "delta.ir", "delta.economy", - "convexity.ir", "convexity.economy", bond_type, value - FROM (SELECT * FROM risk_positions(p_date, 'CRT', fund)) c + SELECT DISTINCT ON (identifier) c.identifier, c.description, a.timestamp, c.notional, c.factor, a.pv, duration_fw, a.dm, "delta.ir", "delta.economy", + "convexity.ir", "convexity.economy", bond_type, value FROM risk_positions(p_date, 'CRT', fund) c LEFT JOIN priced_at_market a on c.identifier = a.cusip - JOIN (SELECT cusip, MAX(timestamp) timestamp - FROM priced_at_market where timestamp between p_date - AND p_date + interval '1 DAY' - and model_des = (select model_version from crt_model_versions where until_date > p_date - order by until_date asc limit 1) GROUP BY cusip) b - using (cusip, timestamp) - LEFT JOIN map_cusip using (cusip) + JOIN crt_model_versions ON model_des=model_version + LEFT JOIN map_cusip USING (cusip) LEFT JOIN bond_types USING (bond) - LEFT JOIN (select bond_type, value from beta_estimates - where date = (SELECT MAX(date) as date FROM beta_estimates where date <= greatest(p_date, DATE '2021-02-01'))) be - using (bond_type); + LEFT JOIN beta_estimates USING (bond_type) + WHERE until_date > p_date + AND timestamp BETWEEN p_date AND p_date + INTERVAL '1 DAY' + AND date <= greatest(p_date, DATE '2021-02-01') +ORDER BY identifier, until_date ASC, timestamp DESC, date DESC; + -- SELECT c.identifier, c.description, c.strategy, a.timestamp, c.notional, c.factor, a.pv, duration_fw, a.dm, "delta.ir", "delta.economy", + -- "convexity.ir", "convexity.economy", bond_type, value + -- FROM (SELECT * FROM risk_positions(p_date, 'CRT', fund)) c + -- LEFT JOIN priced_at_market a on c.identifier = a.cusip + -- JOIN (SELECT cusip, MAX(timestamp) timestamp + -- FROM priced_at_market where timestamp between p_date + -- AND p_date + interval '1 DAY' + -- and model_des = (select model_version from crt_model_versions where until_date > p_date + -- order by until_date asc limit 1) GROUP BY cusip) b + -- using (cusip, timestamp) + -- LEFT JOIN map_cusip using (cusip) + -- LEFT JOIN bond_types USING (bond) + -- LEFT JOIN (select bond_type, value from beta_estimates + -- where date = (SELECT MAX(date) as date FROM beta_estimates where date <= greatest(p_date, DATE '2021-02-01'))) be + -- using (bond_type); END $$ LANGUAGE plpgsql; |