Tuesday, March 18, 2008 12:58 PM
I have a reference table. This table has an effective date and end date, and I need to get a single set of values at a point in time. Suppose the following hotel rating values are the values that I want to pull out of the table:
This value has other information associated with it (benchmarks and such) and so it's possible that one of the 2 star entries gets end-dated, and then a new 2 Star entry gets created with an effective date and no end date.
How can I query so that I only get one 2 Star entry at any point, and get the point in time where the date is between the effective and end date, but only pull back a single set of entries, one distinct rating value. So, suppose the first entry is end-dated 7/1, if I look at old data, I want to see the old benchmarks and so I want the previous entry end-dated 7/1; otherwise, I want to see the new end-date, with the new 2 star entry with no end date.
How do I do that? Make sense?
Tuesday, March 18, 2008 2:43 PM
you can use Distinct extension method:Code Snippet
(from ... in ...
Tuesday, March 18, 2008 5:29 PM
"current data" vs "old data" requirement is captured by specifying a date parameter.
Date AsOfDate = DateTime.Now;
int myHotelID = 1;
var Rating =
from r in myDC.ratings
where r.hotelID == myHotelID
where r.EffectiveDate <= AsOfDate
order by r.EffectiveDate descending
if you want to do this for multiple hotels, you'll have to work a groupby in, and apply the ordering and taking to each group.