Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  Looks easy but hard to implement

Author  Topic  gmetaj Starting Member 33 Posts Posted - 2004-12-15 : 15:00:53 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