diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 11 |
1 files changed, 9 insertions, 2 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index f1afc40c..1d61979f 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2792,12 +2792,13 @@ CREATE TABLE beta_crt( PRIMARY KEY (date, strategy) ); -CREATE OR REPLACE VIEW payment_settlements +CREATE OR REPLACE VIEW payment_settlements2 AS SELECT trades.settle_date, trades.fund, cps.name, trades.cp_code, trades.asset_class, + trades.account, trades.currency, sum(trades.payment_amount)::numeric(10,2) AS payment_amount, array_agg(trades.id) AS ids @@ -2806,6 +2807,7 @@ AS SELECT trades.settle_date, bond_trades.fund, bond_trades.cp_code, 'bond'::text AS asset_class, + 'Account' as account, 'USD'::currency AS currency, CASE WHEN bond_trades.buysell THEN - bond_trades.net_amount @@ -2820,6 +2822,7 @@ AS SELECT trades.settle_date, cds_trades.fund, cds_trades.cp_code, 'tranche'::text AS asset_class, + 'Account' as account, cds_trades.currency, cds_trades.upfront AS payment_amount FROM cds_trades @@ -2830,6 +2833,7 @@ AS SELECT trades.settle_date, swaptions.fund, swaptions.cp_code, 'swaption'::text AS asset_class, + swaptions.cash_account as account, swaptions.currency, CASE WHEN swaptions.buysell THEN (- swaptions.notional) * swaptions.price / 100::double precision @@ -2843,8 +2847,10 @@ AS SELECT trades.settle_date, spots.fund, spots.cp_code, 'spot'::text AS asset_class, + spots.cash_account AS account, unnest(ARRAY[spots.buy_currency, spots.sell_currency]) AS currency, unnest(ARRAY[spots.buy_amount, - spots.sell_amount]) AS amount + FROM spots UNION SELECT fx_swaps.id, @@ -2853,6 +2859,7 @@ AS SELECT trades.settle_date, fx_swaps.fund, fx_swaps.cp_code, 'spot'::text AS asset_class, + fx_swaps.cash_account as account, unnest(ARRAY[near_buy_currency, near_sell_currency, far_buy_currency, far_sell_currency]) AS currency, unnest(ARRAY[near_buy_amount, -near_sell_amount, far_buy_amount, - far_sell_amount]) AS amount FROM fx_swaps @@ -2866,7 +2873,7 @@ AS SELECT trades.settle_date, SELECT accounts.code AS cp_code, accounts.name FROM accounts) cp_code) cps USING (cp_code) - GROUP BY trades.settle_date, trades.fund, cps.name, trades.cp_code, trades.asset_class, trades.currency + GROUP BY trades.settle_date, trades.fund, cps.name, trades.cp_code, trades.asset_class, trades.currency, trades.account ORDER BY trades.settle_date DESC, trades.fund, cps.name, trades.asset_class, trades.currency; |