diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 34 |
1 files changed, 16 insertions, 18 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 56c3f966..a9ba327a 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2800,7 +2800,7 @@ CREATE TABLE beta_crt( PRIMARY KEY (date, strategy) ); -CREATE OR REPLACE VIEW payment_settlements +CREATE OR REPLACE VIEW public.payment_settlements AS SELECT trades.settle_date, trades.fund, cps.name, @@ -2810,12 +2810,12 @@ AS SELECT trades.settle_date, trades.currency, sum(trades.payment_amount)::numeric(10,2) AS payment_amount, array_agg(trades.id) AS ids - FROM ( SELECT bond_trades.id, + FROM ( SELECT bond_trades.id::text AS id, bond_trades.settle_date, bond_trades.fund, bond_trades.cp_code, 'bond'::text AS asset_class, - 'Account' as account, + 'Account'::text AS account, 'USD'::currency AS currency, CASE WHEN bond_trades.buysell THEN - bond_trades.net_amount @@ -2825,28 +2825,28 @@ AS SELECT trades.settle_date, FROM bond_trades WHERE bond_trades.tradeid IS NOT NULL UNION - SELECT cds_trades.id, + SELECT cds_trades.id::text AS id, cds_trades.settle_date, cds_trades.fund, cds_trades.cp_code, 'tranche'::text AS asset_class, - 'Account' as account, + 'Account'::text AS account, cds_trades.currency, cds_trades.upfront AS payment_amount FROM cds_trades WHERE cds_trades.orig_attach IS NOT NULL UNION - SELECT swaptions.id, + SELECT swaptions.id::text AS id, swaptions.settle_date, swaptions.fund, swaptions.cp_code, 'swaption'::text AS asset_class, - 'Bilateral Trade' as account, - 'USD' AS currency, - -swaptions.fee AS payment_amount + 'Bilateral Trade'::text AS account, + 'USD'::currency AS currency, + - swaptions.fee AS payment_amount FROM swaption_trades swaptions UNION - SELECT spots.id, + SELECT spots.dealid::text AS dealid, spots.settle_date, spots.fund, spots.cp_code, @@ -2854,19 +2854,17 @@ AS SELECT trades.settle_date, 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, - unnest(ARRAY[near_settle_date, near_settle_date, far_settle_date, far_settle_date]) AS settle_date, + SELECT fx_swaps.dealid, + unnest(ARRAY[fx_swaps.near_settle_date, fx_swaps.near_settle_date, fx_swaps.far_settle_date, fx_swaps.far_settle_date]) AS 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 - ) trades + fx_swaps.cash_account AS account, + unnest(ARRAY[fx_swaps.near_buy_currency, fx_swaps.near_sell_currency, fx_swaps.far_buy_currency, fx_swaps.far_sell_currency]) AS currency, + unnest(ARRAY[fx_swaps.near_buy_amount, - fx_swaps.near_sell_amount, fx_swaps.far_buy_amount, - fx_swaps.far_sell_amount]) AS amount + FROM fx_swaps) trades LEFT JOIN ( SELECT DISTINCT ON (cp_code.cp_code) cp_code.cp_code, cp_code.name FROM ( SELECT c.code AS cp_code, |