diff options
| -rw-r--r-- | sql/dawn.sql | 14 |
1 files changed, 9 insertions, 5 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 6de6957e..6c07a744 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -766,10 +766,11 @@ CREATE OR REPLACE function latest_sim(p_date date) RETURNS integer AS $$ DECLARE id integer; BEGIN -SELECT MAX(model_id_sub) into id FROM model_versions +SELECT model_id_sub into id FROM model_versions a INNER JOIN model_versions_nonagency USING (model_id_sub) INNER JOIN simulations_nonagency USING (simulation_id) - WHERE (start_time < p_date) AND (description='normal'); + WHERE (start_time < p_date) AND (description='normal') + ORDER BY a.start_time DESC limit 1; RETURN id; End; $$ LANGUAGE plpgsql; @@ -783,6 +784,7 @@ CREATE FOREIGN TABLE priced( timestamp timestamp, pv float, pv_RnW float, + pv_FB float, pv_io float, pv_po float, modDur float, @@ -815,6 +817,7 @@ CREATE FOREIGN TABLE priced_orig_ntl( timestamp timestamp, pv float, pv_RnW float, + pv_FB float, pv_io float, pv_po float, modDur float, @@ -1013,7 +1016,7 @@ CREATE OR REPLACE function list_subprime_data(p_date date, orig_flag bool, VARIA RETURNS TABLE(v1 float, v2 float, v3 float, duration float, percentile5 float, percentile25 float, percentile50 float, percentile75 float, percentile95 float, yield_delta float, wal float, io_pv float, po_pv float, rnw float, ir_io_delta float, ir_po_delta float, hpi_delta float, delta_rnw float, delta_mult float, - v1pv_RnW float, v1_lsdel float, v1_hpidel float, v1_irdel float) AS $$ + v1pv_RnW float, v1_lsdel float, v1_hpidel float, v1_irdel float, pv_FB float) AS $$ DECLARE query text; opt_constraint text; @@ -1028,7 +1031,8 @@ query:= WITH temp AS (SELECT * from %I where date(timestamp)=$1) SELECT a.cusip, a.pv as v1, b.pv as v2, c.pv AS v3, a.modDur, c.delta_yield, c.wal, c.pv_io, c.pv_po, c.pv_RnW, c.delta_ir_io, c.delta_ir_po, - c.delta_hpi, c.delta_RnW, c.delta_mult, a.pv_RnW as v1pv_RnW, a.delta_mult as v1_lsdel, a.delta_hpi as v1_hpidel, a.delta_ir as v1_irdel + c.delta_hpi, c.delta_RnW, c.delta_mult, a.pv_RnW as v1pv_RnW, + a.delta_mult as v1_lsdel, a.delta_hpi as v1_hpidel, a.delta_ir as v1_irdel, c.pv_FB FROM (SELECT * FROM temp WHERE model_version=1 and model_id_sub=$3) a, (SELECT * FROM temp WHERE model_version=2 and model_id_sub=$3) b, (SELECT * FROM temp WHERE model_version=3 and model_id_sub=$3) c @@ -1053,7 +1057,7 @@ SELECT left_table.v1, left_table.v2, left_table.v3, left_table.moddur, right_table.pv5, right_table.pv25, right_table.pv50, right_table.pv75, right_table.pv95, left_table.delta_yield, left_table.wal, left_table.pv_io, left_table.pv_po, left_table.pv_RnW, left_table.delta_ir_io, left_table.delta_ir_po, left_table.delta_hpi, left_table.delta_RnW, left_table.delta_mult, - left_table.v1pv_RnW, left_table.v1_lsdel, left_table.v1_hpidel, left_table.v1_irdel + left_table.v1pv_RnW, left_table.v1_lsdel, left_table.v1_hpidel, left_table.v1_irdel, left_table.pv_FB FROM (SELECT unnest($2) AS cusip) l LEFT JOIN left_table ON left_table.cusip=l.cusip LEFT JOIN right_table ON left_table.cusip=right_table.cusip'; |