Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Unanswered: Detecting last records entered in a table

    Hello,

    I need to select the last record that was entered for a each case_id in a table and then see if that each last record for each case_id was made in the last 7 days.

    This returned query is to populate an alarm that the case has not had enough contact.

    The following code almost works - however it selects the first record in the table for the case and not the last. ie if case_id 3 has four records: 2010-07-01, 2010-08-02, 2010-08-10, 2010-08-11 and todays date is 2010-08-13, it always seems to stop at 2010-07-01 and return that the case has not had enough contact.

    I have tried a number of things but cant quite get it to work.

    Does anyone have a suggestion.

    SELECT case_notes.case_id As CaseID, case_management.contact_time As ContactTime, person.firstname, person.surname FROM case_notes,case_management, person WHERE case_notes.case_id = case_management.case_id AND ( date_sub(curdate(), interval 0 month) - case_notes.whenadded > 7 ) AND case_management.contact_time = 'Weekly' AND case_management.active = 'Active' AND case_management.person_id = person.person_id GROUP BY case_notes.case_id ORDER BY case_notes.case_id

    thanks
    Scott

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DW_Scott View Post
    I need to select the last record that was entered for a each case_id
    Code:
    SELECT case_notes.case_id As CaseID
         , case_management.contact_time As ContactTime
         , person.firstname
         , person.surname 
      FROM ( SELECT case_id
                  , MAX(whenadded) AS last_added
               FROM case_notes
             GROUP
                 BY case_id ) AS m
    INNER
      JOIN case_notes
        ON case_notes.case_id = m.case_id
       AND case_notes.whenadded = m.last_added
    INNER
      JOIN case_management
        ON case_management.case_id = case_notes.case_id
       AND case_management.contact_time = 'Weekly' 
       AND case_management.active = 'Active' 
    INNER
      JOIN person 
        ON person.person_id = case_management.person_id
     WHERE case_notes.whenadded >= CURRENT_DATE - INTERVAL 7 DAY
    ORDER 
        BY case_notes.case_id
    Last edited by r937; 08-14-10 at 01:09.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    4
    Thanks for the reply.

    I see what you are doing but am getting an error

    Unknown column 'm.case_notes' in 'on clause'

    The following works as I need - thanks- the results appear not to be available in the next join.

    ( SELECT case_id
    , MAX(whenadded) AS last_added
    FROM case_notes
    GROUP
    BY case_id ) As m

    I have tried it in phpmyadmin and navicat

    Any further ideas

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DW_Scott View Post
    Unknown column 'm.case_notes' in 'on clause'
    aaargh... typo

    i fixed it, see above
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2010
    Posts
    4
    Thanks that works great - except for one thing.

    If there are two entries for the case_note on the last day entered, the person is listed twice in the result.

    Is there a way to limit the result to only one per case_id.

    Thanks again

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DW_Scott View Post
    Is there a way to limit the result to only one per case_id.
    make sure the whenadded column is DATETIME, not DATE

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2010
    Posts
    4
    Thanks again - I really appreciate your help.

Posting Permissions

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