1 files changed, 46 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 7922af0a..7748dabe 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -3211,6 +3211,52 @@ CREATE TABLE equityoptions (
 	CONSTRAINT equity_options2_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES public.counterparties(code) ON UPDATE CASCADE
 );
 
+
+CREATE OR REPLACE VIEW equityoption_trades AS
+SELECT eo.id,
+       eo.dealid,
+       t.termination_date AS trade_date,
+       t.fee_payment_date AS settle_date,
+       t.termination_amount AS notional,
+       eo.identifier,
+       CASE WHEN t.termination_cp=eo.cp_code THEN
+         'Termination'
+       ELSE
+         'Assignment'
+       END AS trade_type,
+       eo.folder,
+       eo.portfolio,
+       eo.fund,
+       t.termination_cp AS cp_code,
+       cps.name,
+       eo.putcall,
+       eo.expiration_date,
+       eo.strike,
+       NOT eo.buysell AS buysell,
+       -t.termination_fee AS fee
+FROM terminations t RIGHT JOIN equityoptions eo USING (dealid)
+LEFT JOIN counterparties cps ON termination_cp = code
+WHERE termination_date IS NOT NULL
+UNION ALL (
+SELECT eo.id,
+       eo.dealid,
+       eo.trade_date,
+       eo.settle_date,
+       eo.notional,
+       eo.identifier,
+       'New' AS trade_type,
+       eo.folder,
+       eo.portfolio,
+       eo.fund,
+       eo.cp_code,
+       cps.name,
+       eo.putcall,
+       eo.expiration_date,
+       eo.strike,
+       eo.buysell,
+       eo.notional * price / 100
+FROM equityoptions eo JOIN counterparties cps ON cp_code = code) ORDER BY trade_date DESC;
+
 CREATE TABLE bond_tickets (
 	bbg_ticket_id text NOT NULL,
 	"Match" text NULL,