diff options
Diffstat (limited to 'src/main/resources/db/20251117-0-summary-view.sql')
| -rw-r--r-- | src/main/resources/db/20251117-0-summary-view.sql | 65 |
1 files changed, 0 insertions, 65 deletions
diff --git a/src/main/resources/db/20251117-0-summary-view.sql b/src/main/resources/db/20251117-0-summary-view.sql deleted file mode 100644 index a4e8004..0000000 --- a/src/main/resources/db/20251117-0-summary-view.sql +++ /dev/null @@ -1,65 +0,0 @@ --- Yucky slow view, since it's sub-querying for each of the summation fields --- AND has a subquery on the inner join to find the right symbols. Anyways it --- should preview relative strength of treating equity transaction data as --- immutable. Any application on top of this should get the full list of --- records so it can construct helpful views of summations based on the history --- of changes, and/or show price data over time -CREATE VIEW account_symbol_summary_view -AS -SELECT - acc.fiduciary_account_id, - acc.fiduciary_account_name, - acc.fiduciary_account_description, - acc.fiduciary_account_number, - acc.fiduciary_account_created_timestamp, - sym.equity_symbol_id, - sym.equity_symbol_type, - sym.equity_symbol_name, - sym.equity_symbol_managing_company, - (SELECT - SUM(ev.equity_change_quantity * ev.equity_change_type_score) - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ) AS account_symbol_quantity, - (SELECT - printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score) / SUM(ev.equity_change_quantity * ev.equity_change_type_score)) - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ) AS account_symbol_cost_basis_price, - (SELECT - printf( '%.2f', SUM(ev.equity_change_cost_basis_usd * ev.equity_change_type_score)) - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ) AS account_symbol_cost_basis_total_usd, - (SELECT - printf( '%.2f', SUM(ev.equity_change_quantity) * ( - SELECT - iev.equity_change_est_price - FROM equity_change_event iev - WHERE iev.fiduciary_account_id = acc.fiduciary_account_id - AND iev.equity_symbol_id = sym.equity_symbol_id - ORDER BY iev.equity_change_timestamp DESC - LIMIT 1 - )) - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ) AS account_symbol_last_effective_price, - (SELECT - ev.equity_change_timestamp - FROM equity_change_event ev - WHERE ev.fiduciary_account_id = acc.fiduciary_account_id - AND ev.equity_symbol_id = sym.equity_symbol_id - ORDER BY ev.equity_change_timestamp DESC - LIMIT 1 - ) AS account_symbol_last_update_timestamp -FROM fiduciary_account acc -INNER JOIN equity_symbol sym ON EXISTS( - SELECT * - FROM equity_change_event e - WHERE e.fiduciary_account_id = acc.fiduciary_account_id - AND e.equity_symbol_id = sym.equity_symbol_id -);
\ No newline at end of file |