I have two tables in my database... one called Ingredient, and one IngredientPriceHistory. They are fk'd on Ingredient.IngredientID = IngredientPriceHistory.IngredientID.
Everytime an ingredient's price is updated, a row gets added to the IngredientPriceHistory table, with the new price and the date.
I would like to make a view, that contains all the Ingredients with their most recent prices.
Some sample data :
Ingredients
Code: Select all
IngredientID Name
----------- ---------
1 Fish Paste
2 Bread
Code: Select all
IngredientPriceHistoryID IngredientID Price Date
----------------------- ------------ ----- -----
1 1 15 2009-10-01
2 2 5 2009-10-01
3 1 16 2009-10-05
4 2 4.30 2009-10-07
Code: Select all
Ingredient Price
---------- ----
Fish Paste 16
Bread 4.30
Code: Select all
SELECT dbo.Ingredients.IngredientID, MAX(dbo.IngredientPriceHistory.EffectiveDate) AS LatestPrice
FROM dbo.IngredientPriceHistory INNER JOIN
dbo.Ingredients ON dbo.IngredientPriceHistory.Ingredient = dbo.Ingredients.IngredientID
GROUP BY dbo.Ingredients.IngredientID