diff options
| -rw-r--r-- | sql/dawn.sql | 10 |
1 files changed, 9 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index d22ca8b2..e454b175 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4183,4 +4183,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 list_iam(p_date date, p_fund fund) RETURNS TABLE (trade_date date, broker text, clean_folder strategy, start_money numeric(11,2), currency currency, fund fund, folder text) AS $$ BEGIN RETURN QUERY +SELECT si.date as trade_date, si.broker, si.strategy as clean_strat, si.amount::NUMERIC(11,2), +si.currency, si.fund, COALESCE(pfm.folder::text, strategy::text) AS folder +FROM (SELECT *, rank() OVER(PARTITION BY si.broker,si.fund ORDER BY date desc) +FROM strategy_im si WHERE si.fund=p_fund AND si.date<=p_date ORDER BY date DESC) si +LEFT JOIN portfolio_folder_mapping pfm ON pfm.clean_folder=si.strategy::TEXT +WHERE RANK=1 and abs(amount) >= .01; END $$ LANGUAGE plpgsql;
\ No newline at end of file |