1 files changed, 34 insertions, 32 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index e8c3739a..eaaa289d 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -54,24 +54,6 @@ CREATE TABLE bonds(id serial primary key,
        principal_payment float,
        accrued_payment float);
 
-CREATE TABLE position(date date,
-       identifier varchar(12) REFERENCES securities(identifier)
-       notional float,
-       coupon float,
-       factor float,
-       price float,
-       market_value_local float,
-       market_value_usd float,
-       accrued float,
-       days_accrued float,
-       start_accrued_date date,
-       factor_pay_date date,
-       paydown float,
-       writedown float,
-       unsettled_principal float,
-       unsettled_interest float,
-       PRIMARY KEY(identifier, date));
-
 CREATE TABLE securities(identifier varchar(12) PRIMARY KEY,
        cusip varchar(9),
        isin varchar(12),
@@ -105,7 +87,7 @@ CREATE TABLE cashflow_history(
        coupon float,
        PRIMARY KEY (identifier, date));
 
-CREATE TABLE FX(date date PRIMARY KEY,
+CREATE TABLE fx(date date PRIMARY KEY,
        eurusd float,
        cadusd float);
 
@@ -122,7 +104,9 @@ CREATE OR REPLACE function list_positions(p_date date,
        p_class asset_class DEFAULT NULL,
        include_unsettled boolean DEFAULT True)
 RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat,
-              last_settle_date date, principal_payment float, accrued_payment float, bbg_type bbg_type) AS $$
+              curr_cpn float, start_accrued_date date, last_settle_date date,
+              principal_payment float, accrued_payment float, currency currency, daycount day_count,
+              day_bbg_type bbg_type) AS $$
 DECLARE sqlquery text;
 DECLARE asset_opt text;
 DECLARE unsettled_opt text;
@@ -142,7 +126,8 @@ BEGIN
           principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) )
           OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date<=$1)
   SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder,
-  settle_date, temp.principal_payment, temp.accrued_payment, securities.bbg_type
+  securities.coupon, start_accrued_date, settle_date, temp.principal_payment,
+  temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type
   FROM temp LEFT JOIN securities USING (identifier)
   WHERE (temp.notional>0 '||unsettled_opt||') AND paid_down>$1 '||asset_opt
   ||' ORDER BY identifier, settle_date desc';
@@ -152,12 +137,12 @@ $$ LANGUAGE plpgsql;
 
 CREATE MATERIALIZED VIEW factors_history AS
 WITH temp AS
-(SELECT c.date, c.identifier, c.principal, c.principal_bal, c.interest, c.coupon,
+(SELECT c.date, c.identifier, c.principal, c.principal_bal, c.interest, lead(c.coupon) OVER w AS coupon,
                      -c.principal-c.principal_bal+lag(c.principal_bal) OVER w AS losses
               FROM cashflow_history c
               WINDOW w AS (PARTITION BY c.identifier order by c.date asc))
 SELECT date, identifier, principal_bal/face_amount AS factor, principal/face_amount*100 AS principal,
-interest/face_amount*100 AS interest, losses/face_amount*100 AS losses, temp.coupon
+interest/face_amount*100 AS interest, losses/face_amount*100 AS losses, coalesce(securities.coupon, temp.coupon)
 FROM temp JOIN securities USING (identifier);
 CREATE UNIQUE INDEX factors_history_pkey ON factors_history(date, identifier);
 
@@ -231,12 +216,29 @@ RETURN factor;
 END;
 $$ LANGUAGE plpgsql;
 
-SELECT identifier, securities.description,notional, securities.coupon,
-price, price * notional* coalesce(factor,1) AS local_market_value,
-notional * coalesce(factor,1)*yearfrac(start_accrued_date, '2015-08-06', day_count) *
-securities.coupon/100. AS int_acc
-FROM list_positions('2015-08-06', 'CSO')
-JOIN securities USING (identifier)
-LEFT JOIN factors_history('2015-08-06') USING (identifier)
-LEFT JOIN list_marks('2015-08-06') USING (identifier)
-ORDER by identifier asc;
+CREATE OR REPLACE function fx_rate(p_date date) RETURNS TABLE(currency currency, fxrate float) AS $$
+BEGIN
+  RETURN QUERY SELECT unnest(Array['USD', 'EUR', 'CAD'])::currency,
+  unnest(Array[1, eurusd, cadusd]) FROM fx WHERE date=p_date;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE
+(description varchar(32), identifier varchar(12), notional float, price float,
+strategy bond_strat, factor float, local_market_value float, usd_market_value float,
+curr_cpn float, int_acc float) AS $$
+BEGIN
+  RETURN QUERY
+  SELECT a.description, a.identifier, a.notional, c.price, a.strategy, coalesce(b.factor, 1),
+      c.price * a.notional * coalesce(b.factor,1),
+      c.price * a.notional * coalesce(b.factor,1) * fxrate,
+      a.curr_cpn,
+      a.notional * coalesce(b.factor,1) * fxrate *yearfrac(start_accrued_date, '2015-08-06', daycount) *
+      a.curr_cpn/100.
+  FROM list_positions(p_date, p_assetclass) a
+  LEFT JOIN factors_history(p_date) b USING (identifier)
+  LEFT JOIN list_marks(p_date, True) c USING (identifier)
+  LEFT JOIN fx_rate(p_date) USING (currency)
+  ORDER by identifier asc;
+END;
+$$ LANGUAGE plpgsql;