1 files changed, 19 insertions, 17 deletions
diff --git a/sql/quantifi.sql b/sql/quantifi.sql
index d1dd9b85..98e0fcae 100644
--- a/sql/quantifi.sql
+++ b/sql/quantifi.sql
@@ -322,27 +322,29 @@ CREATE TABLE swaptions (
  CREATE SERVER dawndb_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'dawndb');
  CREATE USER MAPPING FOR quantifi_user SERVER dawndb_server OPTIONS (user 'dawn_user', password  '****');
 
-CREATE OR REPLACE
-FUNCTION list_unterminated_tranche(p_start_from date) RETURNS TABLE (date date, productname TEXT, tradeid TEXT) AS $$
+CREATE OR REPLACE FUNCTION list_unterminated_tranche(p_date DATE)
+RETURNS TABLE(tradeid text, productname TEXT, most_recent_tradedate DATE, currentnotional text) AS $$
 BEGIN
-	RETURN query
-	SELECT ct.date, a.productname, ct.tradeid
-FROM(
-	SELECT c.productname, sum(currentnotional)
-FROM cdxtrancheut c
-WHERE c.date = p_start_from
-GROUP BY
-		c.productname
-HAVING
-		sum(currentnotional)= 0) a
-LEFT JOIN cdxtrancheut ct
-	USING (productname);
-
+    RETURN QUERY
+    SELECT t.tradeid, t.productname, m.most_recent_tradedate, t.currentnotional::text
+    FROM cdxtrancheut t
+    JOIN (
+        SELECT c.productname, MAX(c.tradedate) as most_recent_tradedate
+        FROM cdxtrancheut c
+        WHERE c.date = p_date
+        AND c.productname IN (
+            SELECT c2.productname
+            FROM cdxtrancheut c2
+            WHERE c2.date = p_date
+            GROUP BY c2.productname
+            HAVING SUM(c2.currentnotional) = 0
+        )
+        GROUP BY c.productname
+    ) m ON t.productname = m.productname AND t.tradedate = m.most_recent_tradedate
+    WHERE t.date = p_date;
 END;
-
 $$ LANGUAGE plpgsql;
 
-
 CREATE TABLE quantifi_submission(
        id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 	   uploadtime timestamptz,