I'm not entirely clear which dates you want based on your Required date but
try this:

SELECT id, [plan], description, amount, lastupdate
FROM SomeTable AS S
WHERE lastupdate =
(SELECT MAX(lastupdate)
FROM SomeTable
WHERE [plan] = S.[plan]
AND description = S.description
AND lastupdate <= @required_dt)

If that's not it then please give us an example Required date and explain
which rows you would want to return from your sample data based on that
date.

--
David Portas
SQL Server MVP
--

If (description,plan,lastupdate) is unique then my query will indeed return
only one row per description/plan but you didn't tell us what the key of
this table is so I could only guess at your requirements. If
(description,plan,lastupdate) isn't unique then you could get more than one
row per description/plan because there may be more than one "most recent"
date. If that is the case and you still only want one row then maybe you do
need GROUP BY as well (and SUM amount?).

--
David Portas
SQL Server MVP
--

Hi David,

The schema is the following. The front office manager can enter the
information for a current date. This information can include the
InsurancePlanID as int, CopayDesc as field, LastUpdate smalldatetime field
and a very important field - CopayAmount as a string like 10/20/30 or
10/25/35, any combination. The main purpose of the query supposed to
retrieve the active Copay information for a required date and required
InsurancePlanID, that's it. If the copay amount was changed it should be
redefined and the last value should be shown. The problem is that sometimes
we need to show all valid records for a required period of time. Only valid.
The record supposed to be valid if the LastUpdate for this record is the
latest for a required interval. The problem for me was to cut off all
additional records, finding only the latest one for a required period for
every CopayDesc field.

Thanks,
Dmitri Shvetsov
http://members.cox.net/dshvetsov/pictures.htm

[quoted text, click to view]