Getting Distinct Entries in Query

Question

Hello,

 

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:

 

1  Star
2 Star

3 Star

4 Star

5 Star

 

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?

 

Answer 1

Hello,

you can use Distinct extension method:

Code Snippet

(from ... in ...

 ...

 select ...).Distinct()

 

 

 

Answer 2

 

"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

  select r

).Take(1);

 

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.