select max date 1

[spiff2002]

This may be an easy one

I have a price table with different dates. The prices apply according to the current date.

The table is mor eor less like this:
price

item_no effective_date end_date region price
1 12/01/1992 null NY 120
1 10/11/2004 12/31/2004 NY 130
2 12/01/2000 12/31/2000 NY 220
2 12/01/1992 null NY 200
2 01/01/2005 01/31/2005 NY 230
2 12/01/1992 null NJ 250

Null values are possible on the end_date field and it means that there is no end date for that price. Now if I want to know the current price for all items on this table(12/10/2004), the result should be

item_no region price
1 NY 130
2 NY 200
2 NJ 250

if the date was 01/03/2005
then the result will be

item_no region price
1 NY 130
2 NY 230
2 NJ 250

Can somebody help me with this query???

Thank you in advance.

Spiff

 

[PHV]

You may try this:
SELECT P.item_no, P.region, P.price
FROM tblPrice AS P
WHERE P.effective_date=(
SELECT Max(M.effective_date) FROM tblPrice AS M
WHERE M.effective_date<=testDate AND (M.end_date>=testDate OR M.end_date IS NULL)
AND M.item_no=P.item_no AND M.region=P.region
)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244