suizhun
01-20-2010, 08:29 AM
I am trying to calculate the ending inventory cost using weighted average. I have figured out a column for the cost per item (CostperItem), and a column for item on hand(QuantityAvailable). Now I want to multiple those two columns together and get a new column for the result. how can i do that?
This is my code:
SELECT Inventory_Item.ItemName, (
SELECT SUM( TotalAmount / Quantity )
FROM Finance_Expenses, Inventory_ExternalOrder
WHERE OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01'
AND Inventory_Item.ItemID = Inventory_ExternalOrder.ItemID
AND Inventory_ExternalOrder.ExpID = Finance_Expenses.ExpID
) AS CostperItem, IFNULL( (
SELECT SUM( Quantity )
FROM Inventory_ExternalOrder
WHERE Inventory_Item.ItemID = Inventory_ExternalOrder.ItemID
AND OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01' ) , 0
) - IFNULL( (
SELECT SUM( Quantity )
FROM Inventory_InternalOrder
WHERE Inventory_Item.ItemID = Inventory_InternalOrder.ItemID
AND OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01' ) , 0
) AS QuantityAvailable
FROM Inventory_Item
This is my code:
SELECT Inventory_Item.ItemName, (
SELECT SUM( TotalAmount / Quantity )
FROM Finance_Expenses, Inventory_ExternalOrder
WHERE OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01'
AND Inventory_Item.ItemID = Inventory_ExternalOrder.ItemID
AND Inventory_ExternalOrder.ExpID = Finance_Expenses.ExpID
) AS CostperItem, IFNULL( (
SELECT SUM( Quantity )
FROM Inventory_ExternalOrder
WHERE Inventory_Item.ItemID = Inventory_ExternalOrder.ItemID
AND OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01' ) , 0
) - IFNULL( (
SELECT SUM( Quantity )
FROM Inventory_InternalOrder
WHERE Inventory_Item.ItemID = Inventory_InternalOrder.ItemID
AND OrderDate <= '2009-08-31'
AND OrderDate >= '2008-09-01' ) , 0
) AS QuantityAvailable
FROM Inventory_Item