1 files changed, 86 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 58369822..9e64720b 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -1698,3 +1698,89 @@ CREATE TABLE strategy_im(
 )
 
 CREATE TYPE cash_rate AS ENUM('FED_FUND', '1M_LIBOR', '3M_LIBOR');
+
+CREATE OR REPLACE VIEW swaption_trades AS
+SELECT id,
+       dealid,
+       termination_date as trade_date,
+       notional,
+       security_desc,
+       security_id,
+       CASE WHEN termination_cp=cp_code THEN
+         'Termination'
+       ELSE
+         'Assignment'
+       END AS trade_type,
+       folder,
+       termination_cp,
+       name,
+       option_type,
+       expiration_date,
+       strike,
+       NOT buysell AS buysell,
+       -termination_fee AS fee
+FROM swaptions JOIN counterparties ON termination_cp = code WHERE termination_date IS NOT NULL
+UNION ALL (
+SELECT id,
+       dealid,
+       trade_date,
+       notional,
+       security_desc,
+       security_id,
+       'New' AS trade_type,
+       folder,
+       cp_code,
+       name,
+       option_type,
+       expiration_date,
+       strike,
+       buysell,
+       notional * price / 100 * (2* buysell::integer -1.)
+FROM swaptions JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC;
+
+CREATE OR REPLACE VIEW cds_trades AS
+SELECT id,
+       dealid,
+       termination_date as trade_date,
+       notional,
+       security_desc,
+       security_id,
+       CASE WHEN termination_cp=cp_code THEN
+         'Termination'
+       ELSE
+         'Assignment'
+       END,
+       folder,
+       termination_cp,
+       name,
+       orig_attach,
+       orig_detach,
+       attach,
+       detach,
+       CASE WHEN protection = 'Buyer' THEN
+         'Seller'::protection
+       ELSE
+         'Buyer'::protection
+       END AS protection,
+       -termination_fee AS upfront,
+       ref
+FROM cds JOIN counterparties ON termination_cp = code WHERE termination_date IS NOT NULL
+UNION ALL (
+SELECT id,
+       dealid,
+       trade_date,
+       notional,
+       security_desc,
+       security_id,
+       'New',
+       folder,
+       cp_code,
+       name,
+       orig_attach,
+       orig_detach,
+       attach,
+       detach,
+       protection,
+       upfront,
+       ref
+FROM cds JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC;