How to Calculate Avg. Price from the following table

[SMA00]

Hi Friends,


I am Looking for Procedure or SQL Script to Calculate Average price for the following Each item number.

Formula:

(Qty Recd - QtySold) * unit price / (Qty recd - qty sold) for Each item


ItemCode Receipt# Qty Recd. Qty Sold Unitprice

A001 RCT001 10 10 10.00
A001 RCT002 15 5 10.00
A001 RCt003 10 00 20.00

B001 RCT002 3 1 10.00
B001 RCT004 1 0 20.00


OUTPUT:

Itemcode Avg.price

A001 15.00
B001 13.33

In this First need to calculate based on the Rceipt number and than group on itemnumber based on the above formula.

Expert help required.

Thanks
AR

 

[amrita418]

Your output seems to be off.

How is the avg calculated (using qty Recd or Qty Sold)?

I am guessing qty recd but for Item A001 shouldn't it be

450 / 35 = 12.85?

Regards,
AA

 

[amrita418]

Oops I take it back, I should use my glasses more often.

I still have doubts with your formula, Can you walk me through the calculation for item code A001?

Regards,
AA

 

[Sunny4Ever]

My Observations:

Your formula results in a Div 0 error for the first row of data? Is this correct? How does this formula give you an average price ????

Are you actually after something simple like this:

SELECT ItemCode, AVG(UnitPrice) As 'Avg.Price' FROM ITEMS
GROUP BY ItemCode

If not then please give more precise details.

Cheers
-S

 

[SMA00]

Hi Guys

You should ignore the record if QtyRecd - QtySold = 0
becuase there is no need for the item Average caluclation which is not in stock.


Regards
AR

 

[amrita418]

Try this:

select    a.Itemcode, 
          Sum(a.total) / sum(a.qty_recd - a.qty_sold)
from      (select ItemCode,   
                  Receipt#, 
                  (qty_recd - qty_sold) * unit_price total, 
                  qty_recd, 
                  qty_sold
           from   #TableB) a
group by  a.ItemCode

Regards,
AA

 

[Sunny4Ever]

Good job amrita you have reverse engineered the required formula.

One problem I can see however is the case where:

sum(a.qty_recd - a.qty_sold) = 0

For this case you will have to add some extra code or get killed by a div 0 error:

--------------------------------------------------------
select a.Itemcode,
CASE sum(a.qty_recd - a.qty_sold)
WHEN 0 THEN
null
ELSE
Sum(a.total) / sum(a.qty_recd - a.qty_sold)
END
from (select ItemCode,
Receipt#,
(qty_recd - qty_sold) * unit_price total,
qty_recd,
qty_sold
from #TableB) a
group by a.ItemCode