Weighted Average Price of products - datawarehouse
Posted on 2012-09-01
This question is purely around calculation of weighted average price of products exists in a data warehouse. This mean provincial pricing that exists in canad shoul dbe applied too.
Here is what I have done so far but not sure if I missed anything...
I like to calculate the weighted average price for a given pharmaceutical manufacturer's products.
I have the following data in SALES table:
Date Wholesaler Product_UPC Province Price$ QTY PackSize
12 Sep 2011 AAA 782222222222 ON 100 2 100 pills
13 Sep 2011 bbb 782222222222 ON 101 2 100 pills
14 Sep 2011 AAA 782222222222 ON 100 2 100 pills
15 Spe 2011 CCC 782222222222 ON 54 1 100 pills
16 Sep 2011 CCC 782222222222 QC 50 1 100 pills
weighted average price for product 7822222222 in Ontario for the month of september 2011 is: 355$/700pills = 0.51 per pill
Then I need to create a new table in the database, call it weighted average price and store it as:
Year Month product_upc province avg price
2011 Sep 78222222222 ON 0.51
2011 Sep 78222222222 QC 0.5
Is this right?
what is provincial pricing and does this cover provincial pricing?
what if I need to extract this data in a report on weekly basis?