SQL - Updating records based on most recent date

Question

I am having difficulty updating records within a database based on the most recent date and am looking for some guidance. By the way, I am new to SQL.

As background, I have a windows forms application with SQL Express and am using ADO.NET to interact with the database. The application is designed to enable the user to track employee attendance on various courses that must be attended on a periodic basis (e.g. every 6 months, every year etc.). For example, they can pull back data to see the last time employees attended a given course and also update attendance dates if an employee has recently completed a course.

I have three data tables:

1. EmployeeDetailsTable - simple list of employees names, email address etc., each with unique ID
2. CourseDetailsTable - simple list of courses, each with unique ID (e.g. 1, 2, 3 etc.)
3. AttendanceRecordsTable - has 3 columns { EmployeeID, CourseID, AttendanceDate, Comments }

For any given course, an employee will have an attendance history i.e. if the course needs to be attended each year then they will have one record for as many years as they have been at the company.

What I want to be able to do is to update the 'Comments' field for a given employee and given course based on the most recent attendance date. What is the 'correct' SQL syntax for this?

I have tried many things (like below) but cannot get it to work:

UPDATE AttendanceRecordsTable
SET Comments = @Comments
WHERE AttendanceRecordsTable.EmployeeID = (SELECT EmployeeDetailsTable.EmployeeID FROM EmployeeDetailsTable WHERE (EmployeeDetailsTable.LastName =@ParameterLastName AND EmployeeDetailsTable.FirstName =@ParameterFirstName)
AND AttendanceRecordsTable.CourseID = (SELECT CourseDetailsTable.CourseID FROM CourseDetailsTable WHERE CourseDetailsTable.CourseName =@CourseName))
GROUP BY MAX(AttendanceRecordsTable.LastDate)

After much googling, I discovered that MAX is an aggregate function and so I need to use GROUP BY. I have also tried using the HAVING keyword but without success.

Can anybody point me in the right direction? What is the 'conventional' syntax to update a database record based on the most recent date?

Answer 1

So you want to update the AttendantsRecordsTable, and set the comment to the comment in the most recent CourseDetailsTable for each employee?

UPDATE 
  dbo.AttendanceRecordsTable
SET 
  Comments = @Comments
FROM
  CourseDetailsTable cd 
INNER JOIN
  Employee e ON e.EmployeeID = AttendanceRecordTable.EmployeeID
WHERE 
  e.LastName = @LastName 
  AND e.FirstName = @FirstName
  AND cd.CourseName = @CourseName
  AND AttendanceRecordsTable.CourseID = cd.CourseID
  AND AttendanceRecordsTable.LastDate = 
        (SELECT MAX(LastDate) 
           FROM AttendanceRecordsTable a
          WHERE a.EmployeeID = e.EmployeeID 
            AND a.CourseID = cd.CourseID)

I think something like that should work.

You basically need to do a join between the AttendanceRecordTable, which you want to update, and the Employee and CourseDetailsTable tables. For these two, you have defined certain parameters to select a single row each, and then you need to make sure to update only that last AttendanceRecordTable entry which you do by making sure it's the MAX(LastDate) of the table.

The subselect here:

(SELECT MAX(LastDate) 
   FROM AttendanceRecordsTable a
  WHERE a.EmployeeID = e.EmployeeID AND a.CourseID = cd.CourseID)

will select the MAX (last) of the LastDate entries in AttendanceRecordsTable, based on selection of a given employee (e.EmployeeID) and a given course (cd.CourseID).

Pair that with the selects to select the single employee by first name and last name (that of course only works if you never have two John Miller in your employee table!). You also select the course by means of the course name, so that too must be unique - otherwise you'll get multiple hits in the course table.

Marc

Answer 2

Assuming that you primary key on the AttendanceRecordsTable is id:

UPDATE AttendanceRecordsTable SET Comments = @Comments
WHERE AttendanceRecordsTable.id = (
    SELECT AttendanceRecordsTable.id
        FROM EmployeeDetailsTable 
        JOIN AttendanceRecordsTable ON AttendanceRecordsTable.EmployeeID = EmployeeDetailsTable.EmployeeID·
        JOIN CourseDetailsTable ON AttendanceRecordsTable.CourseID = CourseDetailsTable.CourseID
    WHERE
        EmployeeDetailsTable.LastName =@ParameterLastName AND EmployeeDetailsTable.FirstName =@ParameterFirstName AND
        CourseDetailsTable.CourseName =@CourseName
    ORDER BY AttendanceRecordsTable.LastDate DESC LIMIT 1)

Basically, that sub select will first join the attendence, employee and coursedetail tables, extract those rows where the employee's and course details' name match those given by your parameters and limit the output in reverted order to one line. You might want to test that sub-select statement first.

Edit: I just read your posting again, you don't have a single primary key column on AttendanceRecordsTable. Bummer.