SQL - Updating records based on most recent date

sql, sql-server, t-sql

Solution

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

Problem

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: - EmployeeDetailsTable - simple list of employees names, email address etc., each with unique ID - CourseDetailsTable - simple list of courses, each with unique ID (e.g. 1, 2, 3 etc.) - 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?

Original source