Best practices for creating a database / SQL to retrieve chronological data

Question

I am creating a database that will help you keep track of which employees were on a particular training course. I would like to get some recommendations on the best way to design a database.

In particular, every employee should attend the training course every year, and my database should keep a history of all the dates on which they attend the course in the past.

The end user will use the software as a planning tool to help them reserve dates for future courses for employees. When they select this employee, they will see:

• (a) Date of last visit
• (b) The forecast date of future attendance (i.e., the last date of presence + 1 calendar year)

From the point of view of my database, any employee can have several past visits to courses:

EmpName AttandanceDate Joe Bloggs 1st Jan 2007 Joe Bloggs 4th Jan 2008 Joe Bloggs 3rd Jan 2009 Joe Bloggs 8th Jan 2010 

My question is the best way to set up a database to make it easier to get the latest course attendance? In the above example, the most recent would be January 8, 2010.

Is there a good way to use SQL to sort by date and select a MAX date?

My other idea was to add a column named "MostRecent" and just set the value to "TRUE".

 EmpName AttandanceDate MostRecent Joe Bloggs 1st Jan 2007 False Joe Bloggs 4th Jan 2008 False Joe Bloggs 3rd Jan 2009 False Joe Bloggs 8th Jan 2010 True 

I wondered if this would simplify SQL ie

 SELECT Joe Bloggs WHERE MostRecent = 'TRUE' 

In addition, when a user updates the attendance data of each employee (i.e. with the last date of visit), I could use SQL to:

• Find an Employee and Set MostRecent to FALSE
• Add a new entry with the MostRecent parameter to TRUE?

Would anyone recommend a method differently? Or do you have a completely different way to solve this problem?

Answer 1

To get the last date of presence, use the group function MAX, i.e.

 SELECT MAX(AttandanceDate) FROM course_data WHERE employee_name = 'Joe Bloggs' 

To get maximum attendance for all employees:

 SELECT employee_name, MAX(AttandanceDate) FROM course_data GROUP BY employee_name ORDER BY employee_name 

The query above will NOT return data for employees who have not attended any courses. Therefore, you need to execute another request.

 SELECT A.employee_name, B.AttandanceDate FROM employee AS A LEFT JOIN ( SELECT employee_id, MAX(AttandanceDate) AS AttandanceDate FROM course_data GROUP BY employee_id ) AS B ON A.id = B.employee_id ORDER BY A.employee_name 

For employees who did not attend any course, the query will return a NULL AttendanceDate .

Answer 2

The flag is redundant. Another way to get the last day an employee works:

 select top 1 AttandanceDate from course_data WHERE employee_name = 'Joe Bloggs' order by AttandanceDate desc 

Answer 3

This may already be so, but the output from the AttandanceDate columns makes me suspicious that this column may not be a datetime column. Most DBMSs have some type of date, time, and / or date data that will be used to store this information. In the answers, KandadaBoggu and OMG Ponies are perfect. But if you save your dates as strings, you will have trouble trying to make any of your suggestions.

Using the date data type also usually opens up the possibility of obtaining date data, for example:

eg. SELECT YEAR (2008-01-01) will return 2008 as an integer.

Answer 4

If you are using SQL Server 2005 or 2008 or later, you can use row_number() to do something like the following. This will be a list of everyone with their last traffic.

 with temp1 as (select * , (row_number() over (partition by EmpName order by AttandanceDate descending)) as [CourseAttendanceOrder] from AttendanceHistory) select * from temp where CourseAttendanceOrder = 1 order by EmpName 

This can be put into the view so you can use it as needed.

However, if you always focus on one person at a time, it might be more efficient to create a stored procedure that can use expressions such as select max(AttandanceDate) only for the person you are working on.