diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 66 |
1 files changed, 66 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 343f0267..eee85f6e 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2430,3 +2430,69 @@ CREATE TABLE beta_crt( beta_crt float, PRIMARY KEY (date, strategy) ); + +CREATE OR REPLACE VIEW public.payment_settlements +AS SELECT trades.settle_date, + trades.fund, + cps.name, + trades.cp_code, + trades.asset_class, + trades.currency, + sum(trades.payment_amount)::numeric(10,2) AS payment_amount, + array_agg(trades.id) AS ids + FROM ( SELECT bond_trades.id, + bond_trades.settle_date, + bond_trades.fund, + bond_trades.cp_code, + 'bond'::text AS asset_class, + 'USD'::currency AS currency, + CASE + WHEN bond_trades.buysell THEN - bond_trades.net_amount + WHEN NOT bond_trades.buysell THEN bond_trades.net_amount + ELSE NULL::double precision + END AS payment_amount + FROM bond_trades + WHERE bond_trades.tradeid IS NOT NULL + UNION + SELECT cds_trades.id, + cds_trades.settle_date, + cds_trades.fund, + cds_trades.cp_code, + 'tranche'::text AS asset_class, + cds_trades.currency, + cds_trades.upfront AS payment_amount + FROM cds_trades + WHERE cds_trades.orig_attach IS NOT NULL + UNION + SELECT swaptions.id, + swaptions.settle_date, + swaptions.fund, + swaptions.cp_code, + 'swaption'::text AS asset_class, + swaptions.currency, + CASE + WHEN swaptions.buysell THEN (- swaptions.notional) * swaptions.price / 100::double precision + WHEN NOT swaptions.buysell THEN swaptions.notional * swaptions.price / 100::double precision + ELSE NULL::double precision + END AS payment_amount + FROM swaptions + UNION + SELECT spots.id, + spots.settle_date, + spots.fund, + spots.account_code AS cp_code, + 'spot'::text AS asset_class, + unnest(ARRAY[spots.buy_currency, spots.sell_currency]) AS currency, + unnest(ARRAY[spots.buy_amount, - spots.sell_amount]) AS amount + FROM spots) trades + LEFT JOIN ( SELECT DISTINCT ON (cp_code.cp_code) cp_code.cp_code, + cp_code.name + FROM ( SELECT c.code AS cp_code, + c.name + FROM counterparties c + UNION + 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 + ORDER BY trades.settle_date DESC, trades.fund, cps.name, trades.asset_class, trades.currency;
\ No newline at end of file |