Return price in a specific date

  • Thread starter Thread starter Guest
  • Start date Start date
I have a table where I store the variation of prices of product and I have
another table where I store the sales of the products.
The prices of products can change as often as a month or a year and I only
store the dates where the change is made.
I can get the prices of the products in the days they change but not the
other days.
How do I get the price of a product between the dates of change of price?
 
A query similar to this may help (uses a subquery to get the price):


SELECT ProductTableName.ProductID, ProductTableName.ProductName,
PriceTableName.Price
FROM ProductTableName INNER JOIN PriceTableName
ON ProductTableName.ProductID = PriceTableName.ProductID
WHERE PriceTableName.PriceDate = (SELECT Max(P.PriceDate)
FROM PriceTableName AS P WHERE P.PriceDate <=
#1/22/2004#);

(I have arbitrarily used a date of January 22, 2004 as the "date on which
the price was effective -- use a parameter or another field as the date).
 
Thanks a lot!

Ken Snell said:
A query similar to this may help (uses a subquery to get the price):


SELECT ProductTableName.ProductID, ProductTableName.ProductName,
PriceTableName.Price
FROM ProductTableName INNER JOIN PriceTableName
ON ProductTableName.ProductID = PriceTableName.ProductID
WHERE PriceTableName.PriceDate = (SELECT Max(P.PriceDate)
FROM PriceTableName AS P WHERE P.PriceDate <=
#1/22/2004#);

(I have arbitrarily used a date of January 22, 2004 as the "date on which
the price was effective -- use a parameter or another field as the date).
Click to expand...
 
Back
Top