question

gwhenning avatar image
gwhenning asked

Calculate "Expiration Date" Based on Date and Looked Up Value in MS SQL

I am creating an employee training tracking database. I have all of the training classes in a table along with the number of days said training is valid. (e.g. -[TrainingID],[TrainingName], [ExpirationDays]) I want to be able to have the expiration date automatically calculated when a user enters data into the training transactions table ([EmployeeID] foreign key, [TrainingID] foreign key, [TrainingDate] entered by user, [ExpirationDate] calculated based on [ExpirationDays] where TrainingID = currently entered TrainingID)
sql-server-expresscalculated-column
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
It would be best to wrap this logic in a stored procedure, where you pass in the user entered fields, and work out the values for the expiration date. Something along the lines of create proc InsertTraining ( @EmployeeID int, @TrainingID int, @TrainingDate datetime ) as set nocount on insert into TrainingTransactionTable (EmployeeID, TrainingID, TrainingDate, ExpirationDate) select @EmployeeID, @TrainingID, @TrainingDate, dateadd(day, [ExpirationDays], @TrainingDate) from Training where TrainingID = @TrainingID
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you! I realized after I posted this that I don't really need to store the data, just display it on a report so I wound up dropping the field and creating a view. That being said, I do have another application for this answer.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.