Return record for specific date

[Guest]

I have a table that stores price records with a field for the effective date
but there is no field for the inactive date. How can I write a query to
return the price for a date that falls between recorded effective dates?

Example: A record exists for 12/03/2005 and the next is 01/04/2005. How can
I find the effective price on 18/04/2005?

Thanks

 

[Guest]

Its the first record(top 1) less than or equal to your date sorted descending.

 

[Alex White MCDBA MCSE]

you need to use the between function within your query

e.g.

select * from tbltest where mydate is between Format(start_date,
"\#yyyy\-mm\-dd\#") and Format(end_date, "\#yyyy\-mm\-dd\#")

or

select * from tbltest where mydate >= Format(start_date, "\#yyyy\-mm\-dd\#")
and mydate < Format(end_date, "\#yyyy\-mm\-dd\#")

mydate being the field in your table.

both do slightly different things but one should do exactly what you want.

 

[Guest]

No luck so far. With a simple table with say 4 records each with a different
price and with dates that are 1/1/05, 5/1/05, 15/1/05 and 23/1/05 what dates
should I use for the start and end to return the price for 4/1/05? Your
solution returns a range of prices not the one specific to the date demanded.

 

[Van T. Dinh]

****Untested****
SELECT Main.frg_ProductID, Main.Price, Main.EffectiveDate
FROM tblPrice As Main
WHERE Main.EffectiveDate =
(
SELECT Max(Sub.EffectiveDate)
FROM tblPrice As Sub
WHERE (Sub.frg_ProductID = Main.frg_ProductID)
AND (Sub.EffectiveDate <= #04/18/2005#)
)
****SQL ends****

Note: there may be more efficient SQLs