I have a table with a Status field, a Start Date and a End Date field.
I need the Status field to be updated to "Active" as soon as the Start Date is equal to today's date. Also, I want the Status to be updated to "InActive" as soon as the End Date is less than today's date.
The first thing that came to my mind was to schedule a job to run at 12am daily to update the Status field in the records of the table.
However, I am not sure how to do this. Also, if it is the best approach.
I'd appreciate if anyone could tell me how to go about this.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.