DATE BAND QUERY 2

[DBAssam]

I'm having problems grabbing the right record in a set - any help would be gratefully received.

example of records could look something like this:

DATE AMOUNT
03/31/03 $120.00
03/04/03 $100.00
02/28/03 $125.00

On the search screen the user would put in a date and I only want to grab the highest dated record that is <= the date they input. There is never more than one record on a given day. If I put in 03/15/03 I want it to only return the value for 03/04/03 i.e. $100.00

Sorry - I'm pretty sure it's an easy thing, but I can't seem to get it right.

 

[swampBoogie]

A bit pseudocode

select * from t
where dateColumn in (
select max(dateColumn) from t
where dateColumn <= '<yourDateValue>')

 

[Otto]

select * from TABLE
where DATE=
(select max(DATE) from TABLE where DATE<=@InputDate)

or

select top 1 * from TABLE
where DATE<=@InputDate oder by DATE desc