1 files changed, 20 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index e19c3207..4a863ed6 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -496,6 +496,26 @@ LEFT JOIN tranche_price USING (basketid, attach, detach, tenor);
 END
 $$ LANGUAGE plpgsql;
 
+CREATE OR REPLACE function list_swaption_positions_and_risks (p_date date)
+RETURNS TABLE(deal_id varchar(28), security_id varchar(12), maturity date, notional float, swaption_type swaption_type, strike float,
+		expiration_date date, latest_model_date date, delta float, gamma float, vega float) AS $$
+BEGIN
+RETURN QUERY
+select c.dealid, c.security_id, c.maturity, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * c.notional as notional, c.swaption_type, c.strike, c.expiration_date, d.date as latest_model_date, d.delta, d.gamma, d.vega
+from swaptions c
+inner join(
+	select a.dealid, a.date, a.market_value, a.delta, a.gamma, a.vega from swaption_marks as a
+		inner join (
+			select dealid, max(date) as latest_model_date
+			from swaption_marks
+			where date <= p_date
+			group by dealid) as b
+		on a.dealid = b.dealid and date = b.latest_model_date
+	) d on c.dealid = d.dealid
+where c.trade_date <= p_date and c.expiration_date >= p_date;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE OR REPLACE function list_abscds_marks(p_date date)
 RETURNS TABLE(security_id varchar(12), cusip varchar(9), security_desc varchar(32), maturity date,
               notional float, factor float, fixed_rate float, clean_nav float, accrued float) AS $$