Hi there
I need to query my database in the following manner.
I get prices from suppliers which are valid for several months, so I am given a validity like this:
SUPPLIER, VALID_FROM, VALID_TO, VALUE, ITEM
1-PEPE SA, 01/oct/09, 31/dec/09, $1000, XXXX
2-PEPE SA, 01/nov/09, 31/dec/09, $500, XXXX
3-PEPE SA, 01/dec/09, 31/dec/09, $600, XXXX
As you can see, on the first line, $1000 is the cost between 01/oct/09 and 31/dec/09
But on the second line, the supplier has given us a new price, $500, valid from 01/11/09 to 31/dec/09, making redundant the first price (but still has a valid date of 31.dec.09
Again on the 01/dec/09 I get another new rate.
All VALID_TO dates are the same.
So the query I need is, for example, if today is 10/nov/09, I want the query to retrieve only row two
If today happens to be the 10/dec/09, the query should retrieve the 3rd line only.
Hope you can help me
