Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    54

    Unanswered: find value based on max(date)

    I know I have done this before, but cannot for the life of me remember how.

    I am trying to determine return the current (last added) deduction amount for each deduction type for each employee

    Sample Table:
    employee|Deduction_type|Date_entered|Amount
    1|MED|1/1/2007|50
    1|DEPC|1/1/2007|100
    1|MED|1/8/2007|50
    1|DEPC|1/8/2007|100
    1|MED|1/15/2007|150
    2|MED|1/1/2007|35
    2|DEPC|1/1/2007|100
    2|MED|1/8/2007|35
    2|DEPC|1/8/2007|75
    2|MED|1/15/2007|35

    Any suggestions?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t.employee
         , t.Deduction_type
         , t.Date_entered
         , t.Amount
      from Sample as t
    inner
      join (
           select employee      
                , Deduction_type
                , max(Date_entered) as max_date
             from Sample
           group
               by employee      
                , Deduction_type
           ) as m
        on m.employee       = t.employee      
       and m.Deduction_type = t.Deduction_type
       and m.max_date       = t.Date_entered
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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