Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Unanswered: Getting only newest record on join

    Hi! I'm having what I'm sure is a very elementary problem that I'm having trouble figuring out. All my searching has only found solutions for MySQL so I'm kind of at a loss. I have three tables - tblTimesheet, tblTimesheetStatus, and lkpStatus. tblTimesheet records the basic header information for a timesheet, tblTimesheetStatus records the history of the timesheet through the submittal and approval/rejection process, and lkpStatus defines the current status.

    I'm trying to pull a list of every timesheet along with only its current status (the last status entered). A subquery would do great - but I'm not sure how to pass along the PK to it.

    Tables below:
    Code:
    [tblTimesheet]
    TimesheetID	EmployeeNum	PayPeriodEnd	Comments	CreatedDate
    ---------------------------------------------------------------------------
    10036		04573		2010-09-26	NULL		2010-09-30
    10037		01600     	2010-09-26	NULL		2010-10-05
    10038		04573     	2010-09-26	NULL		2010-11-02
    Code:
    [tblTimesheetStatus]
    TimesheetStatusID	TimesheetID	StatusID	Comments	CreatedDate
    -----------------------------------------------------------------------------------
    17			10036		1		NULL		2010-11-02
    18			10037		1		NULL		2010-10-05
    23			10038		1		NULL		2010-11-02
    24			10036		2		NULL		2010-11-02
    25			10036		3		NULL		2010-11-03
    26			10036		2		NULL		2010-11-03
    27			10036		4		NULL		2010-11-04
    Code:
    [lkpStatus]
    StatusID	Status
    -----------------------------------------
    1		Unsubmitted
    2		Awaiting Manager Approval
    3		Rejected
    4		Approved
    5		Accepted
    And finally, my SQL - which obviously isn't doing what I need:
    Code:
    SELECT T.TimesheetID, T.EmployeeNum, T.PayPeriodEnd, 
    LS.[Status], LS.StatusID
    FROM tblTimesheet T
    LEFT JOIN tblTimesheetStatus S ON
    S.TimesheetID = T.TimesheetID 
    LEFT JOIN lkpStatus LS ON
    LS.StatusID = ISNULL(S.StatusID, 1)
    Any help would be much appreciated! And apologies in advance if this is a super obvious solution that I'm missing.

    Thanks!
    -T

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    its a little hard to give you the correct answer as not enough info was supplied. Your timesheetstatus table does not have a time, so we have to guess that your TimesheetStatusID is in order of when the row was inserted.
    If so, then something like:
    Code:
    tblTimesheetStatus S
       ON S.TimesheetID = T.TimesheetID 
      AND s.TimesheetStatusID = (select max(s1.TimesheetStatusID)
                                       from tblTimesheetStatus S1
                                 WHERE S.TimesheetID = T.TimesheetID )
    Dave Nance

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    shouldn't be HAVING MAX(ts) - ts

    ???
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Quote Originally Posted by dav1mo View Post
    its a little hard to give you the correct answer as not enough info was supplied. Your timesheetstatus table does not have a time, so we have to guess that your TimesheetStatusID is in order of when the row was inserted.
    If so, then something like:
    Code:
    tblTimesheetStatus S
       ON S.TimesheetID = T.TimesheetID 
      AND s.TimesheetStatusID = (select max(s1.TimesheetStatusID)
                                       from tblTimesheetStatus S1
                                 WHERE S.TimesheetID = T.TimesheetID )
    Dave Nance
    I think you might've missed something in my previous post. The TimesheetStatus table does indeed have a date - it's DateCreated (the last column on the right).

    But, regardless, your suggestion worked. Thanks!

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    yes I saw the date but that is not good enough to give you the most recent status. What if you have 2 entries for the same day, like your sample data for timesheetid 10036? Which one is most recent then. Without a timestamp you cannot tell. And the info you provided did not tell us if your TimesheetStatusID is a unique and ever increasing number, that was left to us as a guess.
    Dave

Posting Permissions

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