Re: Mysql Nested sub query
Hi Peter,
cost and cost_price are usd_price * exchange_rate * quantity?
For the sake of simplicity, the exchange rate can be ignored at this time and just use the usd_price
What weights are to be used for the weighted averages?
Here is an example performed in Excel to obtain the weighted average cost
NB. Only GRV transactions are used to determine these values
ABC001 100 9.45 945
ABC001 50 9.85 492.5
ABC001 2000 8.25 16500
2150 17937.5
Weighted 8.343023256
Average 9.183333333
Max 9.85
Last 8.25
The weighted average is calculated by multiplying the quantity by the price for each line and then summing this quantity for all lines within the time frame
17,937.50 as shown.
The quantities are also summed within the given time frame and then divided by the total quantity to provide a weighted average of 8.343023256
Average is merely AVG(C1..C3) in excel
Max is MAX(C1..C3)
Last
takes the last GRV Price (by date that falls before the snapshot date)
What is desired when there are multiple transactions per part_code on the last date for which there is a transaction?
The last GRV by Date/Time would be used in this instance where there are multiple GRV's on that date
And for the sample dataset provided, what are the expected results for latest_grv_price_prior_to_the_specified_date, weighted_average_cost_up_to_specified_date, maximum_cost_price_up_to_specified_date ?
As Above
I hope this makes sense
Many thanks for your interest
Subject
Written By
Posted
Re: Mysql Nested sub query
April 20, 2022 12:41AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.