1 files changed, 71 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index c5e12571..639ac249 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -164,3 +164,74 @@ SELECT DISTINCT ON (identifier) * FROM factors_history
        WHERE date<=p_date ORDER BY identifier, date desc;
 END;
 $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION isleapyear (
+  D date
+) RETURNS boolean
+AS $$
+DECLARE
+  y INTEGER;
+BEGIN
+ y := extract (year from D);
+ if (y % 4) != 0 then
+   return false;
+ end if;
+
+ if (y % 400) = 0 then
+   return true;
+ end if;
+
+ return (( y % 100) != 0);
+END;
+$$ LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION yearfrac(date1 date, date2 date, daycount day_count)
+RETURNS float AS $$
+DECLARE
+  factor float;
+  y1 integer;
+  y2 integer;
+  m1 integer;
+  m2 integer;
+  d1 integer;
+  d2 integer;
+BEGIN
+
+IF daycount='30/360' THEN
+   y1 := extract(YEAR FROM date1);
+   y2 := extract(YEAR FROM date2);
+   m1 := extract(MONTH FROM date1);
+   m2 := extract(MONTH FROM date2);
+   d1 := extract(DAY FROM date1);
+   d2 := extract(DAY FROM date2);
+   IF d2=31 and (d1=30 or d1=31) THEN
+      d2:=30;
+   END IF;
+   IF d1=31 THEN
+      d1:=30;
+   END IF;
+   factor:= (360*(y2-y1) + 30*(m2-m1)+d2-d1)/360.;
+ELSIF daycount='ACT/365' THEN
+  factor:=(date2-date1)/365.;
+ELSIF daycount='ACT/360' THEN
+  factor:=(date2-date1)/360.;
+ELSIF daycount='ACT/ACT' THEN
+  IF isleapyear(date1) THEN
+    factor:=(date2-date1)/366.;
+  ELSE
+    factor:=(date2-date1)/365.;
+  END IF;
+END IF;
+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;