Advice on software / database design to avoid using cursors when updating database

Question

I have a database that logs when an employee has attended a course and when they are next due to attend the course (courses tend to be annual).

As an example, the following employee attended course '1' on 1st Jan 2010 and, as the course is annual, is due to attend next on the 1st Jan 2011. As today is 20th May 2010 the course status reads as 'Complete' i.e. they have done the course and do not need to do it again until next year:

EmployeeID    CourseID    AttendanceDate    DueDate     Status
123456        1           01/01/2010        01/01/2011  Complete

In terms of the DueDate I calculate this in SQL when I update the employee's record e.g. DueDate = AttendanceDate + CourseFrequency (I pull course frequency this from a separate table).

In my web based app (asp.net mvc) I pull back this data for all employees and display it in a grid like format for HR managers to review. This allows HR to work out who needs to go on courses.

The issue I have is as follows.

Taking the example above, suppose today is 2nd Jan 2011. In this case, employee 123456 is now overdue for the course and I would like to set the Status to Incomplete so that the HR manager can see that they need to action this i.e. get employee on the course.

I could build a trigger in the database to run overnight to update the Status field for all employees based on the current date. From what I have read I would need to use cursors to loop over each row to amend the status and this is considered bad practice / inefficient or at least something to avoid if you can???

Alternatively, I could compute the Status in my C# code after I have pulled back the data from the database and before I display it on screen. The issue with this is that the Status in the database would not necessarily match what is shown on screen which just feels plain wrong to me.

Does anybody have any advice on the best practice approach to such an issue?

It helps, if I did use a cursor I doubt I would be looping over more than 1000 records at any given time. Maybe this is such small volume that using cursors is okay?

Answer 1

I wouldn't use a trigger for this, but schedule a job, probably within SQL server. A cursor is not required here either, surely it's just:

UPDATE TABLE SET Status = 'Incomplete' WHERE DueDate < GetDate()

Answer 2

Unless I'm missing something from your explanation, there's no need for cursors at all:

UPDATE
    dbo.YourTable
SET
    Status = ‘Incomplete’
WHERE
    DueDate < GETDATE()

It would be preferable not to maintain the DueDate or Status for these records at all. I woudl expect to see Employee, Course, EmployeeCourse and EmployeeCourseAttendance tables, with which you could use the following:

-- Employees that haven't attended a course 
-- within dbo.Course.Frequency of current date
SELECT
    ec.EmployeeID
    , ec.CourseID
    , eca.LastAttendanceDate
    , DATEADD(day, c.Frequency, eca.LastAttendanceDate) AS DueDate
FROM
    dbo.EmployeeCourse ec
INNER JOIN
    dbo.Course c
LEFT OUTER JOIN
    ebo.EmployeeCourseAttendance eca
ON  eca.EmployeeID = ec.EmployeeId
AND eca.CourseID = ec.CourseID
WHERE
    GETDATE() > DATEADD(day, c.Frequency, eca.LastAttendanceDate)

-- Show all employees and status for each course
SELECT
    ec.EmployeeID
    , ec.CourseID
    , eca.LastAttendanceDate
    , DATEADD(day, c.Frequency, eca.LastAttendanceDate) AS DueDate
    , CASE
        WHEN eca.LastAttendanceDate IS NULL THEN 'Has not attended'
        WHEN (GETDATE() > DATEADD(day, c.Frequency, eca.LastAttendanceDate) THEN 'Incomplete'
        WHEN (GETDATE() < DATEADD(day, c.Frequency, eca.LastAttendanceDate) THEN 'Complete'
      END AS Status
FROM
    dbo.EmployeeCourse ec
INNER JOIN
    dbo.Course c
LEFT OUTER JOIN
    ebo.EmployeeCourseAttendance eca
ON  eca.EmployeeID = ec.EmployeeId
AND eca.CourseID = ec.CourseID

Answer 3

You could also use a computed column expression. This way you would never have to update the STATUS column/keep it in sync with dates

create table coureses
(
employeeid int not null,
courseid int not null,
attendancedate datetime null,
duedate datetime null,
[status] as case
    when duedate is null and attendancedate is null then 'n/a'
    when datediff(day,duedate, getdate()) > 0 then 'Incomplete'
    when datediff(day,attendancedate, getdate()) > 0 then 'Complete'
    else 'n/a'
    end
)