diff options
| -rw-r--r-- | python/task_server/globeop.py | 6 | ||||
| -rw-r--r-- | sql/dawn.sql | 11 |
2 files changed, 9 insertions, 8 deletions
diff --git a/python/task_server/globeop.py b/python/task_server/globeop.py index 4f4def68..a69f4abf 100644 --- a/python/task_server/globeop.py +++ b/python/task_server/globeop.py @@ -142,8 +142,10 @@ def insert_todb(workdate): df.to_sql(table, engine, if_exists='append', index=False) def upload_bond_marks(engine, workdate): - df = pd.read_sql_query("SELECT * from list_marks(%s)", engine, - params=(workdate.date(),)) + df = pd.read_sql_query("SELECT identifier, price from list_marks(%s) " + "RIGHT JOIN list_positions(%s, NULL, False) " + "USING (identifier)", engine, + params=(workdate.date(), workdate.date())) df.rename(columns={'identifier': 'IDENTIFIER', 'price': 'Price'}, inplace=True) fullpath = DAILY_DIR / str(workdate.date()) / f"securitiesNpv{workdate:%Y%m%d_%H%M%S}.csv" diff --git a/sql/dawn.sql b/sql/dawn.sql index 1949cf60..b6b675aa 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -489,12 +489,11 @@ CREATE TABLE mark_source_mapping( final text, PRIMARY KEY (globeop)); -CREATE OR REPLACE function list_marks(p_date date, include_unsettled boolean DEFAULT False) -RETURNS TABLE(identifier varchar(12), price float) AS $$ +CREATE OR REPLACE function list_marks(p_date date) +RETURNS TABLE(p_date date, identifier varchar(12), price float) AS $$ BEGIN - RETURN QUERY SELECT a.identifier, b.price FROM list_positions(p_date, Null, include_unsettled) a LEFT JOIN -(SELECT DISTINCT ON (identifier) date, marks.identifier, marks.price FROM marks -WHERE date<= p_date ORDER BY identifier, date DESC) b USING (identifier); + RETURN SELECT DISTINCT ON (identifier) marks.date, marks.identifier, marks.price FROM marks +WHERE date<= p_date ORDER BY identifier, marks.date DESC; END; $$ LANGUAGE plpgsql; @@ -606,7 +605,7 @@ BEGIN b.last_pay_date, a.principal_payment, a.accrued_payment, a.last_settle_date FROM list_positions(p_date, p_assetclass, true, p_fund) a LEFT JOIN factors_history(p_date) b USING (identifier) - LEFT JOIN list_marks(p_date, True) c USING (identifier) + LEFT JOIN list_marks(p_date) c USING (identifier) LEFT JOIN fx_rate(p_date) USING (currency) ORDER by identifier asc; END; |