diff options
Diffstat (limited to 'sql/serenitasdb.sql')
| -rw-r--r-- | sql/serenitasdb.sql | 13 |
1 files changed, 8 insertions, 5 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index dbe5c804..469c5865 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -500,28 +500,31 @@ CREATE OR REPLACE FUNCTION curve_quotes2( date,
float[] DEFAULT '{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[],
bbgsource DEFAULT 'MKIT'::bbgsource) RETURNS
- TABLE(cds_ticker text, weight float, date date, spread_curve float[],
+ TABLE(cds_ticker text, seniority sen, doc_clause shortcode,
+ weight float, date date, spread_curve float[],
upfront_curve float[], recovery_curve float[], currency curr,
event_date date) AS $$
BEGIN
- RETURN QUERY SELECT max(markit_ticker) AS t, max(b.weight), max(a.date),
+ RETURN QUERY SELECT max(markit_ticker) AS t, b.seniority,
+ short_code, max(b.weight), max(a.date),
array_agg((a.runningbid + a.runningask)/2 ORDER BY tenor),
array_agg((a.upfrontbid + a.upfrontask)/2 ORDER BY tenor),
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, seniority, c.weight, c.event_date, c.currency, tenor FROM
+ (SELECT curve_ticker, markit_ticker, company_id, c.seniority, short_code, 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,
+ short_code,
+ d.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, seniority ORDER BY t;
+ ON b.curve_ticker = a.curve_ticker GROUP by company_id, b.seniority, short_code ORDER BY t;
END;
$$ LANGUAGE plpgsql;
|