1 files changed, 39 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 74fef12e..5346d359 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -217,7 +217,7 @@ BEGIN
   ELSE
      unsettled_opt = '';
   END IF;
-  sqlquery := 'WITH temp as (SELECT bonds.identifier, asset_class, settle_date, folder,
+  sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder,
           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,
@@ -230,6 +230,19 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
+CREATE OR REPLACE function list_cds_positions (p_date date)
+RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date, attach smallint,
+        detach smallint, notional float) AS $$
+BEGIN
+
+RETURN QUERY WITH tmp AS (SELECT cds.security_id, cds.security_desc, cds.maturity, cds.attach, cds.detach,
+sum(cds.notional * (CASE WHEN cds.protection='Buyer' THEN 1 ELSE -1 END))
+OVER (PARTITION BY cds.security_id, cds.maturity, cds.attach, cds.detach)  AS notional
+FROM cds WHERE cds.trade_date <=p_date)
+SELECT DISTINCT ON (security_id, maturity, attach) * FROM tmp WHERE tmp.notional!=0;
+END;
+$$ LANGUAGE plpgsql;
+
 CREATE MATERIALIZED VIEW factors_history AS
  WITH temp AS (
          SELECT c.date,
@@ -325,6 +338,20 @@ RETURN factor;
 END;
 $$ LANGUAGE plpgsql;
 
+CREATE OR REPLACE function tranche_factor(attach smallint, detach smallint,
+                  index_factor float, cumulativeloss float)
+RETURNS float AS $$
+DECLARE
+  newattach float;
+  newdetach float;
+
+BEGIN
+newattach:=LEAST(GREATEST((attach-100*cumulativeloss)/index_factor, 0), 1);
+newdetach:=LEAST(GREATEST((detach-100*cumulativeloss)/index_factor, 0), 1);
+RETURN (newdetach-newattach)/(detach-attach)*index_factor;
+END;
+$$ LANGUAGE plpgsql;
+
 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,
@@ -467,6 +494,17 @@ CREATE FOREIGN TABLE index_desc(
        maturity date)
    SERVER postgresql_server;
 
+CREATE FOREIGN TABLE index_version(
+       basketID serial,
+       Index index_type,
+       Series smallint,
+       Version smallint,
+       IndexFactor float,
+       CumulativeLoss float,
+       lastdate date,
+       redindexcode text,
+       PRIMARY KEY(BasketID)
+);
 
 -- #bonds that get written down
 -- update securities set identifier='073879R75_A' where identifier='073879R75';