diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/et_tables.sql | 46 |
1 files changed, 36 insertions, 10 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 93a2682d..d403252a 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -351,12 +351,12 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) BEGIN RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager,NULL, a.Spread/100, b."Deal Issue Date", b."Reinv End Date", b.Maturity, - CAST(NULL AS float), Cast(NULL AS float), CAST(NULL AS FLOAT), CAST(NULL AS float), - a.Curr_Attach/100, (a.Curr_Detach-a.Curr_Attach)/100 + CAST(NULL AS float), b."CDO Percentage", CAST(NULL AS FLOAT), CAST(NULL AS float), + d.subordination, d.thickness FROM cusip_universe a LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c - ON a.cusip = c.cusip ORDER BY c.id; + ON a.cusip = c.cusip LEFT JOIN et_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; END; $$ LANGUAGE plpgsql; @@ -364,7 +364,8 @@ ALTER FUNCTION et_cusip_details(varchar(10)[]) OWNER TO et_user; CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) - RETURNS TABLE(p_cusip varchar(9), p_orig_subordination float, p_curr_subordination float) AS $$ + RETURNS TABLE(p_cusip varchar(9), p_orig_subordination float, p_curr_subordination float, + p_orig_thickness float, p_curr_thickness float) AS $$ DECLARE cusip_row RECORD; curr_cumsum_cusip_bal float; @@ -375,8 +376,10 @@ CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) defaulted_price float:=0.7; curr_subordination float[]; orig_subordination float[]; + curr_thickness float[]; + orig_thickness float[]; deal_info RECORD; - i int := 0; + i int := 1; previous_moody text; cusips varchar(9)[]; BEGIN @@ -396,16 +399,39 @@ CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) curr_subordination := curr_subordination || (curr_adjusted_collat_bal-curr_cumsum_cusip_bal)/deal_info."Curr Deal Bal"; orig_subordination := orig_subordination || (orig_adjusted_collat_bal - orig_cumsum_cusip_bal)/deal_info."Orig Deal Bal"; cusips := cusips || cusip_row.Cusip; - i:=i+1; - IF i>0 THEN + IF i=1 THEN + curr_thickness := curr_thickness || 1 - curr_subordination[i]; + orig_thickness := orig_thickness || 1 - orig_subordination[i]; + ELSE + curr_thickness := curr_thickness || curr_subordination[i-1] - curr_subordination[i]; + orig_thickness := orig_thickness || orig_subordination[i-1] - orig_subordination[i]; + END IF; + if i>1 THEN IF cusip_row.orig_moody = previous_moody THEN - curr_subordination[i-1]:=curr_subordination[i]; - orig_subordination[i-1]:=orig_subordination[i]; + curr_subordination[i-1] := curr_subordination[i]; + orig_subordination[i-1] := orig_subordination[i]; + IF i=2 THEN + curr_thickness[i-1] := 1 - curr_subordination[i-1]; + orig_thickness[i-1] := 1 - orig_subordination[i-1]; + ELSE + curr_thickness[i-1] := curr_subordination[i-2] - curr_subordination[i-1]; + orig_thickness[i-1] := orig_subordination[i-2] - orig_subordination[i-1]; + END IF; END IF; END IF; + i := i+1; previous_moody := cusip_row.orig_moody; END LOOP; - RETURN QUERY SELECT unnest(cusips), unnest(orig_subordination), unnest(curr_subordination); + RETURN QUERY SELECT unnest(cusips), unnest(orig_subordination), unnest(curr_subordination), unnest(orig_thickness), unnest(curr_thickness); END; $$ LANGUAGE plpgsql; +CREATE TABLE et_model_numbers( + Cusip varchar(9) references cusip_universe(Cusip), + subordination float, + thickness float, + wap_basis float, + PRIMARY KEY(Cusip) + ); + +GRANT ALL ON et_model_numbers TO et_user; |