diff options
| -rw-r--r-- | sql/serenitasdb.sql | 29 |
1 files changed, 17 insertions, 12 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 7c626867..dbe5c804 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -510,33 +510,38 @@ CREATE OR REPLACE FUNCTION curve_quotes2( array_agg(a.Recovery ORDER BY tenor),
max(b.currency), max(b.event_date) FROM historical_cds_quotes($2, $4) a
RIGHT JOIN
- (SELECT curve_ticker, markit_ticker, company_id, c.weight, c.event_date, c.currency, tenor FROM
+ (SELECT curve_ticker, markit_ticker, company_id, seniority, c.weight, c.event_date, c.currency, tenor FROM
(SELECT unnest('{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[]) AS tenor,
unnest(cds_curve) AS curve_ticker,
markit_ticker,
company_id,
+ seniority,
d.weight,
d.currency,
d.event_date
FROM index_members($1, $2) d) c
WHERE tenor=Any($3)) b
- ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
+ ON b.curve_ticker = a.curve_ticker GROUP by company_id, seniority ORDER BY t;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION curve_quotes_fmt(varchar(4), date, bbgSource DEFAULT 'MKIT') RETURNS
- TABLE(cds_ticker text, date date, spread_curve text, upfront_curve text, recovery_curve text) AS $$
- BEGIN
- RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
+TABLE(cds_ticker text, seniority sen, date date, weight float, spread_curve text,
+ upfront_curve text, recovery_curve text) AS $$
+ BEGIN
+ RETURN QUERY SELECT max(markit_ticker) AS t, b.seniority, max(a.date), max(b.weight),
string_agg(to_char((a.runningbid+a.runningask)/2, 'FM999'), ',' ORDER BY tenor),
string_agg(to_char((a.upfrontbid+a.upfrontask)/2, 'FM99D99'), ',' ORDER BY tenor),
- string_agg(to_char(a.Recovery, 'FM0D99'), ',' ORDER BY tenor) FROM historical_cds_quotes($2, $3) a
- RIGHT JOIN
- (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
- FROM index_members($1, $2)) b
- ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
- END;
- $$ LANGUAGE plpgsql;
+ string_agg(to_char(a.Recovery, 'FM0D99'), ',' ORDER BY tenor)
+ FROM historical_cds_quotes($2, $3) a
+ RIGHT JOIN (SELECT
+ generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker,
+ markit_ticker, company_id, c.seniority, c.weight
+ FROM index_members($1, $2) c) b
+ ON b.curve_ticker = a.curve_ticker
+ GROUP by company_id, b.seniority ORDER BY t;
+ END;
+$$ LANGUAGE plpgsql;
CREATE TABLE index_quotes_old(
date date,
|