Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1609
  • Last Modified:

Weighted Average Price of products - datawarehouse

Hi,

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?

Thanks
0
shmz
Asked:
shmz
  • 7
  • 5
  • 2
1 Solution
 
5teveoCommented:
SQL modeling of problem is found below...

your questions
"Is this right?" SQL model is right - i don't know drug business formula
and
"what is provincial pricing and does this cover provincial pricing?"
i don't know drug business formula




use student


DECLARE @tTable1 TABLE
                                                (
                                                Id      int,                   
        DateVal DATETIME,
                                                product char(30) null,
                                                province char(30) null,
                                                price decimal(10,5) null,
                                                qty int null,
                                                lotsize int null
                                                )
 
INSERT INTO @tTable1 (Id,DateVal, product, province, price, qty, lotsize)
            values
                        (1, '9/1/2012', '782222222222', 'on', 100, 2, 100)

INSERT INTO @tTable1 (Id,DateVal, product, province, price, qty, lotsize)
            values
                        (1, '9/1/2012', '782222222222', 'on', 101, 2, 100)

INSERT INTO @tTable1 (Id,DateVal, product, province, price, qty, lotsize)
            values
                        (1, '9/1/2012', '782222222222', 'on', 100, 2, 100)

INSERT INTO @tTable1 (Id,DateVal, product, province, price, qty, lotsize)
            values
                        (1, '9/1/2012', '782222222222', 'on', 54, 1, 100)

INSERT INTO @tTable1 (Id,DateVal, product, province, price, qty, lotsize)
            values
                        (1, '9/1/2012', '782222222222', 'qb', 50, 1, 100)



select * from @tTable1

select Sum(price), Sum(qty * lotsize), round(Sum(price)/Sum(qty * lotsize),2) FROM @tTable1
group by province
0
 
shmzAuthor Commented:
I like to know hoe to calculate using simple math , figuering how to handle data.
0
 
sdstuberCommented:
the sample syntax above is for sql server but the general idea was correct.

the math is simple, it's just a normal average.  sum the price and divide by the total quantity.  It's not really weighted at all.


if your pack size is constant, you can simply use the price and qty columns and divide by 100


SELECT TO_CHAR(saledate, 'yyyy') year,
       TO_CHAR(saledate, 'Mon') month,
       product_upc,
       province,
       ROUND(SUM(price$) / SUM(qty))/100 avg_price
  FROM sales
GROUP BY TO_CHAR(saledate, 'yyyy'),
         TO_CHAR(saledate, 'Mon'),
         product_upc,
         province;

if your packsize might vary then you'll have to parse it since it includes both a numeric value (100) and a non-numeric value (' pills')

SELECT TO_CHAR(saledate, 'yyyy') year,
       TO_CHAR(saledate, 'Mon') month,
       product_upc,
       province,
       ROUND(SUM(price$) / SUM(qty * TO_NUMBER(REGEXP_SUBSTR(packsize, '[0-9]+'))), 2) avg_price
  FROM sales
GROUP BY TO_CHAR(saledate, 'yyyy'),
         TO_CHAR(saledate, 'Mon'),
         product_upc,
         province;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
shmzAuthor Commented:
so what is the Weighted Average Price?
0
 
5teveoCommented:
This is appearing to be a math question? not a SQL query?

The Weighted Average Price in inventory systems that i've worked on is:
total cost of product / number of units.  

You have added the need to break out Weighted Average Price by Month/Region (i.e. Ontario or Quebue) - so you perform calculation for these Month/Regions combo.

Extra... not sure it matters-- but...
This compuation yeilds current Weighted Average Price can use for accounting purposes to determine average basis cost for product. Knowing the average basis cost of product for item relieves inventory application / accounting system from having to track specific lots of product with differing costs when purchased. I shared this as I am familiar with inventory / accounting systems using weighted averages BUT I don't know if Drug industry uses something unique formula-wise.  

The 'provincial pricing' reference is confusing me a little. I am wanting to guess it is related to regional / site pricing hence SQL will work to average 'Province' column cost.

When you reference  ''Provincial pricing' are you refering to you column name or some special Drug industry pricing model?
0
 
shmzAuthor Commented:
Provincial pricing: I have been told a given drug can be sold at different prices in different provinces.

I mostly want to use the weighted average pricing in reports we generate for manufacturers or wholesalers as we should not disclose individuals' actual sales to others. are we on the right track?
0
 
sdstuberCommented:
Provincial pricing: - the query posted above does that


weighted average - what is it you think you want to weight?  

Weighting means you count some members more or less than the others.  
Are you doing that?  If so, which members and in what way are they counted differently?

If you are not counting anything differently then you do not have or want a weighted average.
0
 
shmzAuthor Commented:
for this question, if I am going to generate report on daily basis or at country level, can I still use the same weighted average pricing table calculated on monthly bais at provincial level?
0
 
sdstuberCommented:
since you are not using a weighted average it's not possible to answer that.

you can use the normal, unweighted average above on daily or or monthly levels
you simply change the grouping to be by whatever time range you are interested in
0
 
shmzAuthor Commented:
Well when average price is multiplied by actual total units sold then weighted average is achieved right?
0
 
shmzAuthor Commented:
This means I calculate average price on monthly basis but to calculate weighted average sale for a particular month I just multiply that total units sold by average price given for that month to get weighted average sales. Now if I want to generate the report for 10 days falling within 2 diff months, what should be done?
0
 
sdstuberCommented:
no you have nothing weighted at all.

"weighted" means you are counting something extra or less than others.

Do you have one province that counts more?  Some days or months?  Some products that count more or less than others?  if not you have nothing weighted.

You can multiply average price by monthly quantity to get an average monthly sale approximation.    However, if your average price is calculated using only the same month then you're simply getting a total sale for the month, not an average.  The only difference might be rounding errors.


If you really want something to be weighted, please open a new question and explain what your weighting criteria is.

I'm not sure what you mean for your other question.  But since it is another question; please open a new question and provide examples of what you are looking for.
0
 
shmzAuthor Commented:
The initial question was about calculation of weighted average sales. Reason for doing so is to not display the actual sales amount. What I am trying to do is to weight the actual sales of a drug so that when I multiply the price by total units sold then actual sales are weighted. Is this clear?
0
 
sdstuberCommented:
The words are clear.  The math is not.

You keep using the term "weighted" but you've given no description as to what values would be counted or summed differently from other values..  Nothing you have shown or described is a weighted value.

The queries in http:#a38359947  give you the results you requested which are NOT weighted

If you are trying to get new results that are different than you requested please open a new question and provide sample data along with the results you expect for that data.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now