Return correct data for a given date.

Question

All,

I have two table one call Employee and the other table call Target Ratio. They are related via FK EmployeeID

tblEmployee
EmployeeID FirstName LastName
1              John     Doe

tblTargetRatio
TargetRatioID EmployeeID EffectiveDate Ratio
      1            1        1/1/2012     8
      2            1        6/1/2012     5
      3            1        9/1/2012     7

My question is how can I query tblTargetRatio table to return correct record for the following cases:

1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8)
2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7)
3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)

Thanks, Alan

Answer 1

It appears you are looking for the most recent effective date. You can do this by looking for the max value less than the value being searched (where @Date is the date being searched):

select top 1 * from tblTargetRatio
where employeeid = 1 and effectivedate < @Date
order by effectivedate desc

Demo: http://www.sqlfiddle.com/#!3/f6d4b/2