Return price in a specific date

C

Claudio Bogado Pompa

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?
 
K

Ken Snell

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).
 
C

Claudio Bogado Pompa

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...
 
Top