I have a user profile which stores a number representing a portfolio value (currency). I have another table which lists recommended investment purchases, with the percentage of the portfolio to be allocated to each fund by risk factor. I need to recalculate the dollar value of the fund purchases when ever the percentages change, or when the portfolio value changes.
I don't want to create a "real" table for every client just to repeat the portfolio value for the calculations. How can I construct a query to select the portfolio value for the client and include it for every row for the calculations to use?
User PortfolioValue
1 200,000
2 50,000
-----------------------------
Asset Fund Risk1Pct Risk2Pct
CashE MM 6% 4%
Fixed PIMCO 4% 6%
US Equity UEF 50% 75%
Desired Sample Output for User 1 via table generator (visual table, not database table)
Asset Fund Risk1Pct R1Calculation Risk2Pct R2Calculation
CashE MM 6% (6% * 200,000) 4% (4% * 200,000)
Fixed PIMCO 4% (4% * 200,000) 6% (6% * 200,000)
US Equity UEF 50% (50% * 200,000) 75% (75% * 200,000)
The portfolio value has been added to wp_usermeta, which has that key - value structure. The portfolio information is in a table called demodb.
The Risk Percentages are in floating point.
Update -------------------------------------------- I have the values I need added into usermeta (user_id, meta_key, meta_value).
There are 9 company tables with the investment options for each company (company_id, assetclass, fundname, risk1pct, risk1action, risk2pct, risk2action, risk3pct, risk3action, risk3pct, risk4action).
I created a table userportfolio (user_id, company_id, total401k, companystockamt, loansamt, withdrawalsamt).
I'm trying to get everything connected so that I have out put like user_id, company_id, assetclass, fundname, (risk1pct * total401k), risk1action, (risk2pct * total401k), risk2action, (risk3pct * total401k), risk3action, (risk4pct * total401k), risk4action.
I can't tell whether I actually need the userportfolio table. Is this info what was meant by the schema?
Assuming your % values are decimals (and that I understand the question), we can do this with a simple join:
Let's call your first table u (users) and your second table ri (recommended investments)
SELECT
u.User,
u.PortfolioValue,
ri.Asset,
ri.Fund,
ri.Risk1Pct,
(u.PortfolioValue * ri.Risk1Pct) AS `R1Calculation`,
ri.Risk2Pct,
(u.PortfolioValue * ri.Risk2Pct) AS `R2Calculation`
FROM table1 `u`
JOIN table2 `ri`