diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 23 |
1 files changed, 15 insertions, 8 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 07d34ced..85127f4d 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -975,17 +975,24 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), index index_ty factor float, coupon float, duration float, theta float, price float, closespread float, clean_nav float, accrued float, globeop_nav float, globeop_notional float) AS $$ - +DECLARE + sqlquery text; + and_clause text; BEGIN -RETURN QUERY -SELECT l.*, b.globeop_nav, b.globeop_notional from list_cds_marks_pre(p_date, strat, 'SERCGMAST') l +IF strat IS NOT NULL THEN + and_clause := 'AND strat = ltrim($2::text, ''SER_'')::strategy'; +ELSE + and_clause := ''; +END IF; +sqlquery := format('SELECT l.*, b.globeop_nav, b.globeop_notional from list_cds_marks_pre($1, $2, ''SERCGMAST'') l JOIN (SELECT a.security_id, a.maturity, sum(endbooknav) AS globeop_nav, sum(endqty) AS globeop_notional - FROM (SELECT periodenddate as date, endqty, endbooknav, split_part(invid, '_', 2) AS security_id, - split_part(invid, '_', 6)::date AS maturity - FROM valuation_reports where invid like 'CDS\_%') a - GROUP BY date, a.security_id, a.maturity HAVING date=p_date) b using (security_id, maturity); -END; + FROM (SELECT periodenddate as date, 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, a.security_id, a.maturity HAVING date=$1) b using (security_id, maturity)', and_clause); +RETURN QUERY EXECUTE sqlquery USING p_date, strat; +end; $$ LANGUAGE plpgsql; |