Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Unanswered: Return correct data for a given date.

    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

  2. #2
    Join Date
    Nov 2012
    Location
    Russia. Kursk
    Posts
    3
    select tmp.ItemId, tmp.InvoiceDate, tr.Ratio from TargetRatio as tr,
    (
    select i.EmployeeID, i.iDate, max(it.EffectiveDate) as EffectiveDate
    from TargetRatio as tr, InputTbl as i
    where i.EmployeeID = tr.EmployeeID
    and i.iDate >= rest.EffectiveDate
    group by i.EmployeeID, i.iDate
    ) as tmp
    where tr.EmployeeID = tmp.EmployeeID
    and tr.EffectiveDate = tmp.EffectiveDate

  3. #3
    Join Date
    Nov 2012
    Posts
    2
    Thank you I got it figured out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •