Calculating Weighted Average - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating Weighted Average

Author

Topic

knitterkuba
Starting Member

1 Post

Posted - 2011-03-21 : 20:26:10

Hi,
I have a bit of a problem calculating the average weighted price of an item. The formula is as follows:
[(Purchase Quantity1/Total Quantity)Price1]+[(Purchase Quantity2/Total Quantity)Price2]....
On top of it, I have different types of items that need to have their price calculated. To make it even worse, it needs to be up to a given date (HAVING (((Purchase_Event.PurchDate)<=[Enter Date]))
Below are tables that will be needed for the calculations. Any help would be appreciated.

Purchase_Event
PurchaseNumber PurchDate PurchaseAmount
PO-1 1/10/2011 $20,000.00
PO-2 1/16/2011 $47,000.00
PO-3 1/29/2011 $16,000.00
PO-5 2/5/2011 $12,600.00
PO-6 2/12/2011 $17,800.00
PO-7 2/20/2011 $10,000.00
PO-8 3/14/2011 $4,625.00
PurchaseInventory_Stockflow
PurchaseNumberInventoryCodePurchaseQuantityUnitCost
PO-1 7432 6000 $2.00
PO-1 8519 2000 $4.00
PO-2 4888 20000 $1.00
PO-2 6784 3000 $9.00
PO-3 5862 1600 $10.00
PO-5 7432 2000 $2.00
PO-5 8519 2000 $4.30
PO-6 7432 4000 $2.30
PO-6 8519 2000 $4.30
PO-7 6784 1000 $10.00
PO-8 6784 500 $9.25

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-21 : 20:40:00

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

I am guessing you want:

SELECT purch_date, (??) AS unit_price_wgt_avg
FROM Purchases, Inventory_Stockflow
WHERE ..;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources