Unanswered: Detecting last records entered in a table
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
I need to select the last record that was entered for a each case_id
SELECT case_notes.case_id As CaseID
, case_management.contact_time As ContactTime
FROM ( SELECT case_id
, MAX(whenadded) AS last_added
BY case_id ) AS m
ON case_notes.case_id = m.case_id
AND case_notes.whenadded = m.last_added
ON case_management.case_id = case_notes.case_id
AND case_management.contact_time = 'Weekly'
AND case_management.active = 'Active'
ON person.person_id = case_management.person_id
WHERE case_notes.whenadded >= CURRENT_DATE - INTERVAL 7 DAY