Unanswered: Automatically Update Record Based on Today's Date and Future Date
I am fairly new with SQL Server and don't know how to approach this problem.
I have an Audit table with a "AuditStatus" and "ScheduleDate" column that contains the day the audit has been scheduled (mm/dd/yyyy format). I also have two tables that contains the calendar quarters and what months are associated with each quarter (Q1:1,2,3; Q2:4,5,6, Q3:7,8,9; Q4:10,11,12).
The audit can be scheduled to a calendar quarter without a schedule date. However, if there is no schedule date, and today's date is two weeks before the assigned quarter begins, the audit status needs to be changed.
For example: Let's say I schedule an audit for Q4 2011 today without a schedule date. Then September 16th 2011 arrives. There is still no schedule date for the audit. SQL Server must somehow automatically update the Audit's status to "Delinquent" because October 1st is the first day of the 4th quarter.
So, is there a way for SQL Server to automatically update the "AuditStatus" column if today's date is two weeks before the beginning of an assigned quarter?