diff options
| -rw-r--r-- | sql/dawn.sql | 4 |
1 files changed, 3 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index c6f45400..c4ac2c4d 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1826,13 +1826,15 @@ SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, tranche_risk.upfront, tranche_risk.running, index_refprice, index_refspread, index_duration, initial_margin_percentage FROM tranche_risk +LEFT join LATERAL +(SELECT full_globeop_id FROM id_mapping WHERE id_mapping.serenitas_id=tranche_id and date < tranche_risk.date ORDER by date desc LIMIT 1) b ON true LEFT JOIN cds ON (tranche_id=id) RIGHT JOIN (SELECT invid, periodenddate, sum(endbookunrealincome) AS globeop_accrued, sum(endbooknav-endbookunrealincome) AS globeop_clean_nav, sum(endqty) AS globeop_notional FROM valuation_reports GROUP BY invid, periodenddate) a - ON (invid=full_globeop_id AND periodenddate=tranche_risk.date) + ON (invid=b.full_globeop_id AND periodenddate=tranche_risk.date) LEFT JOIN index_version ON (security_id=redindexcode) LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx USING (date, currency) LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date |