diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 24 |
1 files changed, 16 insertions, 8 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 24c00a2d..f94621ad 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1661,13 +1661,13 @@ sqlquery := format(' SELECT l.*, b.globeop_nav, b.globeop_notional FROM list_cds_marks_pre($1, $2, $3) l JOIN (SELECT a.security_id, a.maturity, sum(endbooknav) AS globeop_nav, sum(endqty) AS globeop_notional - FROM (SELECT periodenddate as date, - fund, - endqty, - endbooknav, - split_part(invid, ''_'', 2) AS security_id, - split_part(invid, ''_'', 6)::date AS maturity - FROM valuation_reports WHERE invid LIKE ''CDS\_%%'' %s) a + FROM (SELECT periodenddate as date, + fund, + endqty, + endbooknav, + split_part(invid, ''_'', 2) AS security_id, + split_part(invid, ''_'', 6)::date AS maturity + FROM valuation_reports WHERE invid LIKE ''CDS\_%%'' %s) a GROUP BY date, fund, a.security_id, a.maturity HAVING date=$1 AND fund=$3) b USING (security_id, maturity)', and_clause); -- ELSIF p_fund = 'BOWDST' THEN @@ -4171,4 +4171,12 @@ SELECT id, upfront, settle_date, swap_type -FROM irs JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder;
\ No newline at end of file +FROM irs JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder; + + +CREATE OR REPLACE FUNCTION compare_globeop_quotes(p_date date, p_fund fund) RETURNS TABLE (identifier TEXT, maturity date, INDEX index_type, security_desc TEXT, serenitas_quote NUMERIC(6,2), globeop_quote NUMERIC(6,2)) AS $$ BEGIN RETURN QUERY +SELECT a.security_id::text, a.maturity::date, a.INDEX, a.security_desc::text, price::NUMERIC(6,2), endlocalmarketprice::numeric(6,2) FROM list_cds_marks(p_date, NULL, p_fund) a LEFT JOIN (SELECT + endlocalmarketprice , + split_part(invid, '_', 2) AS security_id, + split_part(invid, '_', 6)::date AS maturity + FROM valuation_reports WHERE invid LIKE 'CDS\_%%' AND periodenddate =p_date AND fund= p_fund) b USING (security_id, maturity); END $$ LANGUAGE plpgsql;
\ No newline at end of file |