diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 234 |
1 files changed, 37 insertions, 197 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 54515e27..44f77ee6 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3054,20 +3054,8 @@ CREATE TABLE beta_crt( PRIMARY KEY (date, strategy) ); -CREATE OR REPLACE FUNCTION get_bond_settlements() -RETURNS TABLE ( - id text, - settle_date date, - fund fund, - cp_code text, - asset_class text, - account text, - currency currency, - payment_amount numeric(11,2) -) AS $$ -BEGIN - RETURN QUERY - SELECT bt.id::text AS id, + +CREATE OR REPLACE VIEW bond_settlements AS SELECT bt.id::text AS id, bt.settle_date, bt.fund::fund, bt.cp_code::text, @@ -3081,24 +3069,8 @@ BEGIN END AS payment_amount FROM bond_trades bt LEFT JOIN accounts a ON bt.account=a.code WHERE bt.tradeid IS NOT NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION get_tranche_settlements() -RETURNS TABLE ( - id text, - settle_date date, - fund fund, - cp_code text, - asset_class text, - account text, - currency currency, - payment_amount numeric(11,2) -) AS $$ -BEGIN - RETURN QUERY - SELECT +CREATE OR REPLACE VIEW tranche_settlements AS SELECT ct.id::text, ct.settle_date, ct.fund::fund, @@ -3109,69 +3081,27 @@ BEGIN ct.upfront::numeric(11,2) AS payment_amount FROM cds_trades ct WHERE orig_attach IS NOT NULL; -END; -$$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION get_tranchepayment_settlements() -RETURNS TABLE ( - id text, - settle_date date, - fund fund, - cp_code text, - asset_class text, - account text, - currency currency, - payment_amount numeric(11,2) -) AS $$ -BEGIN - RETURN QUERY - SELECT tc.tranche_id::TEXT AS id, tc.date AS settle_date, + +CREATE OR REPLACE VIEW tranchepayment_settlements AS SELECT tc.tranche_id::TEXT AS id, tc.date AS settle_date, cds.fund::fund, cds.cp_code::text, 'TRANCHE_CF' AS asset_class, 'OTC'::text AS account, tc.currency::currency, (COALESCE(tc.principal,0) + COALESCE(tc.accrued,0))::numeric(11,2) AS payment_amount - FROM tranche_cashflows tc LEFT JOIN cds ON tranche_id=cds.id ; -END; -$$ LANGUAGE plpgsql; + FROM tranche_cashflows tc LEFT JOIN cds ON tc.tranche_id=cds.id ; -CREATE OR REPLACE FUNCTION get_swaption_settlements() -RETURNS TABLE ( - id text, - settle_date date, - fund fund, - cp_code text, - asset_class text, - account text, - currency currency, - payment_amount numeric(11,2) -) AS $$ -BEGIN - RETURN QUERY - SELECT swaptions.id::text AS id, - swaptions.settle_date, - swaptions.fund::fund, - swaptions.cp_code::text, + + +CREATE OR REPLACE VIEW swaption_settlements AS SELECT st.id::text AS id, + st.settle_date, + st.fund::fund, + st.cp_code::text, 'SWAPTION' AS asset_class, 'OTC'::text AS account, 'USD'::currency AS currency, - - swaptions.fee::numeric(11,2) AS payment_amount - FROM swaption_trades swaptions; -END; -$$ LANGUAGE plpgsql; + - st.fee::numeric(11,2) AS payment_amount + FROM swaption_trades st; -CREATE OR REPLACE FUNCTION get_trs_settlements() -RETURNS TABLE ( - id text, - settle_date date, - fund fund, - cp_code text, - asset_class text, - account text, - currency currency, - payment_amount numeric(11,2) -) AS $$ -BEGIN - RETURN QUERY - SELECT tt.id::text, +CREATE OR REPLACE VIEW trs_settlements AS SELECT tt.id::text, tt.settle_date, tt.fund::fund, tt.cp_code::text, @@ -3180,23 +3110,8 @@ BEGIN 'USD'::currency AS currency, tt.upfront::numeric(11,2) AS payment_amount FROM trs_trades tt; -END; -$$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION get_spot_settlements() -RETURNS TABLE ( - id text, - settle_date date, - fund fund, - cp_code text, - asset_class text, - account text, - currency currency, - payment_amount numeric(11,2) -) AS $$ -BEGIN - RETURN QUERY - SELECT st.dealid::text AS dealid, +CREATE OR REPLACE VIEW spot_settlements AS SELECT st.dealid::text AS dealid, st.settle_date, st.fund::fund, st.cp_code::text, @@ -3205,24 +3120,8 @@ BEGIN unnest(ARRAY[st.buy_currency, st.sell_currency])::currency AS currency, unnest(ARRAY[st.buy_amount, - st.sell_amount])::numeric(11,2) AS amount FROM spots st LEFT JOIN accounts2 a USING (cash_account); -END; -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION get_fxswap_settlements() -RETURNS TABLE ( - id text, - settle_date date, - fund fund, - cp_code text, - asset_class text, - account text, - currency currency, - payment_amount numeric(11,2) -) AS $$ -BEGIN - RETURN QUERY - SELECT ft.dealid::text, +CREATE OR REPLACE VIEW fxswap_settlements AS SELECT ft.dealid::text, unnest(ARRAY[ft.near_settle_date, ft.near_settle_date, ft.far_settle_date, ft.far_settle_date]) AS settle_date, ft.fund::fund, ft.cp_code::text, @@ -3231,92 +3130,33 @@ BEGIN unnest(ARRAY[ft.near_buy_currency, ft.near_sell_currency, ft.far_buy_currency, ft.far_sell_currency])::currency AS currency, unnest(ARRAY[ft.near_buy_amount, - ft.near_sell_amount, ft.far_buy_amount, - ft.far_sell_amount])::numeric(11,2) AS amount FROM fx_swaps ft LEFT JOIN accounts2 a USING (cash_account); -END; -$$ LANGUAGE plpgsql; CREATE OR REPLACE VIEW payment_settlements -AS SELECT trades.settle_date, - trades.fund, - cps.name, - trades.cp_code, - trades.asset_class, - trades.account, - trades.currency, - sum(trades.payment_amount)::numeric(11,2) AS payment_amount, - array_agg(trades.id) AS ids - FROM ( SELECT bond_trades.id::text AS id, - bond_trades.settle_date, - bond_trades.fund, - bond_trades.cp_code, - 'BOND' AS asset_class, - bond_trades.account, - '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 +AS SELECT t.settle_date, + t.fund, + c.name, + t.cp_code, + t.asset_class, + t.account, + t.currency, + sum(t.payment_amount)::numeric(11,2) AS payment_amount, + array_agg(t.id) AS ids + FROM ( SELECT * FROM bond_settlements UNION - SELECT cds_trades.id::text AS id, - cds_trades.settle_date, - cds_trades.fund, - cds_trades.cp_code, - 'TRANCHE' AS asset_class, - 'Bilateral Trade'::text AS account, - cds_trades.currency, - cds_trades.upfront AS payment_amount - FROM cds_trades - WHERE cds_trades.orig_attach IS NOT NULL + SELECT * FROM tranche_settlements UNION - SELECT tranche_id::TEXT AS id, tc.date AS settle_date, - cds.fund, cds.cp_code, 'TRANCHE_PAYMENT' AS asset_class, - 'Bilateral Trade'::text AS account, tc.currency, - (COALESCE(tc.principal,0) + COALESCE(tc.accrued,0))::numeric(11,2) AS payment_amount - FROM tranche_cashflows tc LEFT JOIN cds ON tranche_id=cds.id + SELECT * FROM tranchepayment_settlements UNION - SELECT swaptions.id::text AS id, - swaptions.settle_date, - swaptions.fund, - swaptions.cp_code, - 'SWAPTION' AS asset_class, - 'Bilateral Trade'::text AS account, - 'USD'::currency AS currency, - - swaptions.fee AS payment_amount - FROM swaption_trades swaptions + SELECT * FROM swaption_settlements UNION - SELECT spots.dealid::text AS dealid, - spots.settle_date, - spots.fund, - spots.cp_code, - 'SPOT' 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 + SELECT * FROM spot_settlements UNION - 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' AS asset_class, - 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, - 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, trades.account - ORDER BY trades.settle_date DESC, trades.fund, cps.name, trades.asset_class, trades.currency; - + SELECT * FROM fxswap_settlements + UNION + SELECT * FROM trs_settlements ) t + LEFT JOIN counterparties c on t.cp_code=c.code + GROUP BY t.settle_date, t.fund, c.name, t.cp_code, t.asset_class, t.currency, t.account + ORDER BY t.settle_date DESC, t.fund, c.name, t.asset_class, t.currency; CREATE TABLE equities ( |