diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 37 |
1 files changed, 24 insertions, 13 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 4a78f354..01de35ff 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2283,24 +2283,35 @@ 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), 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 c.identifier, a.timestamp, c.notional, c.factor, a.pv, duration_fw, a.dm, "delta.ir", "delta.economy", +-- 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 - left join priced_at_market a on c.identifier = a.cusip - JOIN (SELECT cusip, MAX(timestamp) timestamp + 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 + 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; @@ -4000,4 +4011,4 @@ CREATE TABLE bbh_infuse_upload( principal decimal NOT NULL, interest decimal NOT NULL, CONSTRAINT unique_upload_check UNIQUE (tradeid, cusip, principal, interest) - );
\ No newline at end of file + ); |