MySQL Forums
Forum List  »  Newbie

Re: Mysql Nested sub query
Posted by: Ken Beauchamp
Date: April 20, 2022 12:41AM

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

Options: ReplyQuote


Subject
Written By
Posted
April 19, 2022 10:56PM
April 19, 2022 11:14PM
April 19, 2022 11:15PM
April 19, 2022 11:24PM
April 19, 2022 11:34PM
April 20, 2022 12:03AM
Re: Mysql Nested sub query
April 20, 2022 12:41AM
April 20, 2022 09:57AM
April 20, 2022 11:23PM
April 21, 2022 04:04AM
April 21, 2022 07:52AM


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.