I have a table named Holding_Value that has several fields in it among
which are UID, fkHolding, EffDate, Units, MarketValue, AssetPrice. UID
is an identity field and fkHolding is a foreign key to a different
table. EffDate is the the effective date while units and marketvalue
are values stored in the table.
what i'm trying to do is get all the values (fkHolding, Effdate, Units,
MarketValue) for all fkHolding for a specific date. That would be
pretty easy if there each unique fkHolding had a corresponding value
for every date. The exception is that if no date is found than you
would have to get the next date less then or equal ot the query date.
To furhter explain assume that there 100 records in the table and there
are only 10 distinct fkHolding values. My result will need to include
only 10 records. Each record will have the values of the row containing
the values less than or equal to the given date for a specific given
date. so if given date (EffDate) is 12/1/2004 and 5 of the 10 distinct
fkHolding have been priced on that date, than we get those values, the
rest 5 rows in the resultset need to be the values of of the latest
date less than the given date.

Now the second problem is that this needs to be efficient because this
is only a part of my subquery and the table does not have 100 records
but a few million records. Now what i can do is get the latest value if
i were given an fkHolding for example i would write

declare @fkHolding as integer
declare @DateValue as datetime
select @fkHolding = 2981
select @DateValue = '9/2/2004'

select Holding_Values.UID, Holding_Values.EffDate,
Holding_Values.fkHolding, Holding_Values.AssetPrice,
Holding_Values.MarketValue
from Holding_Values INNER JOIN
(select max(Holding_Values.effdate) as DatePriced from
Holding_Values INNER JOIN
(select * from Holding_values where fkHolding = @fkHolding and
Holding_Values.EffDate < @DateValue) as a
on a.UID = Holding_values.UID ) as b
on Holding_Values.EffDate = b.DatePriced and Holding_Values.fkHolding =
@fkHolding

or also would write it in the same way taking a different approach:

declare @fkHolding as integer
declare @DateValue as datetime
select @fkHolding = 2981
select @DateValue = '9/2/2004'

select Top 1 Holding_Values.UID, Holding_Values.EffDate,
Holding_Values.fkHolding,
Holding_Values.AssetPrice, Holding_Values.MarketValue from
Holding_Values INNER JOIN
(select * from Holding_values where fkHolding = @fkHolding and
Holding_Values.EffDate < @DateValue) as a
on a.UID = Holding_values.UID
Order by Holding_Values.EffDate desc

Both these queries produce a row each when ran for a specific date and
fkHolding. Now the challege is to get all the latest distinct fkHolding
values given only a date.
Thank you for your time and help.

Gent Metaj