Sql-server – Change a field in Database on a Particular Date

sql serverupdate

There is a field in my database named GRADES, what I want to accomplish is that on a particular date I want to update all the grades of a specific value to a new value. For example, let's say 5 weeks from today's date I want to update Grades from I to F.

Thanks in Advance

Note: I cannot do anything to the database like create a new table or a new field it should be in the same table.

Best Answer

If I were starting from scratch, I might build this logic into a computed column. This may not be the best idea, but I figured I'd throw it out there as an alternative.

CREATE TABLE dbo.Grades
(
    StudentiD BIGINT,
    CourseId BIGINT,
    CourseGrade VARCHAR(2),
    FailDate DATE,
    FinalGrade AS CASE WHEN GETDATE() >= FailDate
                            AND CourseGrade = 'I' THEN 'F'
                       ELSE CourseGrade
                  END
);

When data gets inserted, you can have a default for (and a modifiable by teachers/admins) FailDate.

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 1, 'A', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 2, 'I', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 3, 'B', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 4, 'I', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 5, 'C', GETDATE() )

This allows them to give extensions or permanently get rid of the concept by making the value NULL.

So the query

SELECT *
FROM dbo.Grades AS g

Gives us:

StudentiD   CourseId    CourseGrade FailDate    FinalGrade
1           1           A           2017-05-19  A
1           2           I           2017-05-19  F
1           3           B           2017-05-19  B
1           4           I           2017-05-19  F
1           5           C           2017-05-19  C
Related Question